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

Take account of the following:

  • 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 have 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

To start working with the tool, click Merge Single Table with Several Lookup Tables in the Merge Tables drop-down menu on the Ablebits Data tab:
Select the tool in the drop-down menu.
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 the scenario 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.

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.

You might be interested in reading the following help pages: