Merge a table with multiple lookups in Excel

The Merge Single Table with Several Lookup Tables tool updates the main table fetching the necessary records from numerous lookup tables with a few mouse clicks.
Take a look at the tool in action.
It can also keep merge scenarios and process your data automatically when needed.

Before you start

Before running the add-in, here are the key points to take note of:

  • Unsaved workbooks cannot be processed. Before running the tool, either close or save them.
  • The tool does not process hidden rows.
  • The whole main table is updated. It is not possible to update only a part of it.
  • Unlike Merge Two Tables, Merge Single Table with Several Lookup Tables does not allow adding columns with the same name in Step 5.
  • If there is a check on the left of the Back up this worksheet option in Step 1, the add-in will create a backup copy of the table that is going to be updated. Clear this checkbox if you do not want to keep the original data your main table contains.
Note. It is not possible to undo the changes the add-in has made.

How to start

On the Ablebits Data tab, in the Merge group, click Merge Tables > Merge Single Table with Several Lookup Tables:
Select the tool in the drop-down menu.

Tip. Hover the cursor over a blue circled question mark to let the add-in's hints become visible.

Find a hint here.

Note. The help page for Merge Two Tables provides a detailed description of the steps the tools have in common.

How to add other lookup tables

You can choose worksheets containing the necessary lookup tables in Step 6 of the add-in:
Pick additional lookup tables.

Note. All the lookup tables you select must be structured alike. If header rows are missing, the number and sequence of columns containing data (including empty columns that are before the tables) must coincide.

If a row with the same key value is present in several lookup tables, the main table will be updated with the data from each of them consecutively in the order you see the lookup tables on the list in Step 6. In the end, the updated columns of the main table will contain the values brought from the table that is the last of those lookup tables.

Scenarios

Selected tables and options can form a scenario of your choice. If you save and run it, the add-in will bring the necessary data from the selected lookup tables to the specified main one considering the options enabled and you need not go through all the steps anew.

How to save scenarios

To make the add-in memorize the tables and options you selected, click the Save scenario button in Step 7:
Just click the Save scenario button.
Then enter the name of your scenario and click OK.

How to run scenarios

Once your scenario is saved, it will be shown each time you run the tool.

To get your main table updated, select a scenario, decide which worksheet to use as the main table, and click Start:
Here are the saved scenarios.

Note. Saved scenarios will not work if the workbooks containing the worksheets to be processed have been moved to another location. You can find your saved scenarios and update the location of the files mentioned there by following this path: C:\Users\UserName\Documents\Ablebits\Merge Tables. You can also create new scenarios instead.
Note. For a saved scenario to work for the current worksheet, the structure of the current worksheet must be the same as that of the main table from the scenario.
Note. If a scenario that was created for the main and lookup tables from one and the same workbook is run for the current worksheet, the required tables will be looked for in the current workbook.

If you do not need any of your saved scenarios, simply remove it from the list by clicking the Delete this scenario icon on the right of the scenario name.