Excel: Merge tables by matching column data or headers (2024)

How to join tables with VLOOKUP

If you are to merge two tables based on one column, VLOOKUP is the right function to use.

Supposing you have two tables in two different sheets: the main table contains the seller names and products, and the lookup table contains the names and amounts. You want to combine these two tables by matching data in the Seller column:

As you see, the order of the names in the main table does not correspond with that in the lookup table, therefore a simple copy/pasting technique won't work.

To combine two tables by a matching column (Seller), you enter this formula in C2 in the main table:

=VLOOKUP($A2,'Lookup table'!$A$2:$B$10,2,FALSE)

Where:

• $A2 is the value you are looking for.
• 'Lookup table'!$A$2:$B$10 is the table to search (please pay attention that we lock the range with absolute cell references).
• 2 is the number of the column from which to retrieve the value.

Copy the formula down the column, and you will get a merged table consisting of the main table, plus the matched data pulled from the lookup table:

Please be aware that Excel VLOOKUP has several limitations, the most critical of which are 1) inability to pull data from a column to the left of the lookup column and 2) a hardcoded column number breaks a formula when you add or remove columns in the lookup table. On the bright side, you can easily reorder the returned columns simply by changing the number in the col_index_num argument.

Tip. If you have an Excel 365 subscription, then you can use a more powerful successor of VLOOKUP - Excel XLOOKUP function.

How to merge tables in Excel with INDEX MATCH

If you are looking for a more powerful and versatile alternative to the VLOOKUP function, embrace this INDEX MATCH combination:

INDEX (return_range, MATCH (lookup_value, lookup_range, 0))

The syntax is explained in detail in this tutorial: INDEX / MATCH in Excel. And here I will show you how to use this formula to look up from right to left, something that VLOOKUP is unable to do.

Let's say you have another lookup table with order IDs in the first column and you wish to copy those IDs to the main table by matching the seller names. For better visualization, both tables are put on the same sheet:

To accomplish the task, you supply the following arguments to the Index Match formula:

• Return_range­ ­- $E$2:$E$10
• Lookup_value - $A2
• Lookup_range - $F$2:$F$10

Please notice the $ sign that locks the ranges to prevent them from changing as you copy the formula down the table:

The completed formula looks as follows:

=INDEX($E$2:$E$10, MATCH($A2, $F$2:$F$10, 0))

…and combines data from two tables perfectly:

In Excel 365, you can use the new XLOOKUP function for the same purpose:

=XLOOKUP(A2, $F$2:$F$10, $E$2:$E$10, "Not found")

How to combine tables by matching multiple columns

If the two tables you wish to join do not have a unique identifier, such as an order id or SKU, you can match values in two or more columns by using this formula:

INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Note. It is an array formula, so please remember to press Ctrl + Shift + Enter to enter it correctly.

The formula's breakdown can be found here: Look up with multiple criteria. For now, let's focus on the practical usage.

Assuming you have the following two tables to be combined into one. Because the Order ID column is missing in the lookup table, the only way to match the orders is by Seller and Product:

Based on the above screenshot, let's define the arguments for our formula:

• Lookup_table - $F$2:$H$9
• Lookup_value1 - $B2
• Lookup_range1 - $F$2:$F$9
• Lookup_value2 - $C2
• Lookup_range2 - $G$2:$G$9
• Return_column_number­ ­- 3

Again, be sure to fix all the ranges with absolute cell references so that they won't change when you copy the formula down:

=INDEX($F$2:$H$9, MATCH(1, ($B2=$F$2:$F$9) * ($C2=$G$2:$G$9), 0), 3)

Enter the formula in D3, press Ctrl + Shift + Enter, copy it to the below rows and check the result:

To have a closer look at the above examples and probably reverse-engineer the formulas, you are welcome to download our sample workbook to Merge Two Tables in Excel.

Example 1. Combine two tables by multiple columns

If you find the array formula for columns match hard to remember, rely on our add-in to do the job quickly and perfectly.

For this example, we will be using the already familiar tables and join them based on 2 columns, Seller and Product. Please note that the lookup table has 2 more columns than the main table:

With the Merge Tables Wizard added to your Excel ribbon, here's what you need to do:

1. Select any cell within your main table and click the Merge Two Tables button on the Ablebits Data tab:
2. Make sure the add-in got the range right, and click Next:
3. Select the lookup table, and click Next:
4. Specify the column pairs to match, Seller and Product in our case, and click Next:

   Tip. If the text case in the key columns matters, check the Case-sensitive matching box to treat uppercase and lowercase as different characters.

5. Optionally, choose the columns to update with the values from the lookup table. Since there is nothing to update in the Order IDs column, we leave it unselected, and simply click Next.
6. Select the columns to add to the main table and click Next.
7. In this step, you tell the wizard how exactly you want the tables to be merged. All the options have descriptive labels, so I won't go into long explanations. If you are unsure about a certain option, click the question mark next to it, and a small diagram will show you how the tables are going to be combined.

The default options work just fine in our case, so we click Finish without changing anything:

Allow the wizard a few seconds for processing and review the result:

As you can see in the screenshot above, the wizard has done the following:

1. Added the Amount column by matching the seller name and product in both tables.
2. Added the Status column that allows you to easily filter matching and new rows. If you don't want it, clear the corresponding box in the final step.
3. New rows that were present only in the lookup table were copied to the end and highlighted in blue.
   • If you don't want to highlight new rows, unselect Set background color for all added rows in the last step.
   • If you don't want to add new rows, unselect Add non-matching rows to the end of the main table in the last step.

Example 2. Join tables and update selected columns

In case your main table contains some outdated data, you can have it updated with the corresponding values from the lookup table.

As an example, let's merge 2 tables by Order ID and update the values in the Price column:

To get the result shown in the above image, this is what you need to do:

Step 1. Select the main table.

Step 2. Select the lookup table.

Step 3. Choose Order ID as the matching column.

Step 4. Select Price as the column to update.

Step 5. Skip it because there are no columns to add.

Step 6. Since there are a few gaps in the New price column, we choose to update only if cells in the lookup table contain data. Optionally, you can highlight the updated cells with any color of your choosing. The screenshot below shows the settings:

Tip. To prevent overwriting your existing data, you can update only empty cells in the main table.

Example 3. Merge multiple matches from two tables

In situations when a lookup table contains several occurrences on the lookup value, you may want to pull them all to your main table. The task can be accomplished with one of the non-trivial array formulas described in Vlookup to return multiple matches in Excel. Or you can do it the easy way with the Merge Tables Wizard.

Supposing your main table contains just one order of each seller, and the lookup table contains additional orders. Now you want to combine all the orders in one table, grouped by seller name like this:

Looks like a lot of work to do? Not if you have the Merge Tables Wizard at your disposal :)

Step 1. Select the main table.

Step 2. Select the lookup table.

Step 3. Choose Seller as the column to match.

Step 4. Update Order ID and Product.

Step 5. There are no columns to add.

Step 6. Insert additional matching rows after the row with the same key value. Optionally, set a background color for added rows to review the changes with a quick glance:

The above examples show just 3 of many possible ways to join tables in Excel. If you are curious to see other scenarios that the Merge Table Wizards can handle, please check out the visuals on this page. Or you can download a 30-day trial version and give it a shot.