VLOOKUP Function (Same Sheet)

This workflow shows how to perform a VLOOKUP function between two tables in the same sheet, using KNIME Analytics Platform.

The workflow inserts the country names on the Athlete Bio table based on the Olympic country codes dictionary table.

Let's walk through the different nodes involved in this operation:

To view the configuration for each node, either Double-click or right-click > "Configuration" to open the configuration dialogue.

Excel Reader node:

1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (the folder with the data is already included when you download the workflow)

2 - Within the "Data Area" tab, we have selected read the "whole sheet" and unflagged skip "empty rows". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.

Value Lookup node:

1 - As a Lookup column (data table), we are taking column "A" from the table where we want to append the complete country names containing the country codes.

2 - Our key column (dictionary table) is "I". It contains the country codes, and column "J" has the country's full name.

3 - Finally, we select column "J" from the dictionary table (the right table in the sheet) with the full country names to be appended.
A new column is appended by default at the end of the table, and since we already have a "J" column, the new column will be named automatically "J(#1)".

Column renamer node:

We are renaming the "F" column into "J(#1)" because the "Table Updater" will insert all the VLookup values there.

Table Updater node:

On top, we pass our original sheet, without the lookup values, with the column "F" renamed into "J(#1)". At the bottom, we pass the sheet with the appended country names in the column "J(#1)".

The Table Updater node compares the input tables and updates the top table cells in column "J(#1)" with the country names from the bottom table.

Excel Writer:

We will append a new sheet called "vlookup_value_appended" in the original excel file (notice that everytime you execute this node the appended sheet is updated).

As you can see from the output, we have the same sheet structure with the entire country names appended to the left table.