VLOOKUP Function - Same Sheet
This workflow shows how to perform a VLOOKUP function in KNIME Analytics Platform. In this example we perform the VLOOKUP between two tables in the same sheet.
The goal is to insert the full country names into the athletes' bio information table (in column "F") based on the country code. The dictionary table is saved in the same spreadsheet.
💡 To view each node's configuration, select the node and see the configuration pane on the right side of the workflow editor.
Let's walk through the different nodes involved in this operation:
Excel Reader node:
Since the folder with the data is already included when you download the workflow, in the "File and Sheet" tab, we choose to "Read from" the "Current workflow data area" and select the dataset.
We select the sheet "By name": "VLOOKUP_same_sheet".
In the "Data Area" tab, we select to read the "Whole sheet" and unflag to skip "empty rows". This configuration allows us to read the sheet as it is. The intent is to respect its original structure.
Value Lookup node:
We connect the input data to both ports of the Value Lookup node.
As lookup column in the input data table, we define column "A"; as key column in the dictionary table, we use column "I". In the include/exclude panel, we only include the column "J" as this is the column that contains the country names.
The Value Lookup node appends column "J" as "J (#1) to the input table.
Column Renamer node:
In a separate branch, we rename column "F" to "J (#1)" to match the column name where the Value Lookup node added the country names. We need to ensure identical column names to apply the Table Updater node properly.
Table Updater node:
At the top port, we pass our original input data (without the lookup values) with the column "F" renamed into "J (#1)". At the bottom port, we pass the table with the appended country names in the column "J (#1)".
The Table Updater node compares inputs and updates the content of matching cell in the top input table. A cell is matching if they share the same column name and RowID.
We have now updated our input data table so that column "F" was replaced by the full country names.
Excel Writer node:
We append the dataset to a new sheet called "vlookup_value_appended" in the existing Excel file located in the workflow data area.
We unflag the "Write column headers" checkbox to maintain the original table structure.
After executing the node, the file will open automatically.
As you can see from the output, we have the same sheet structure and in addition we appended the full country names to the left table.