How to Merge Two Tables in Excel With XLOOKUP or Power Query

To merge two tables in Excel, you match their rows on a column they share, such as a customer ID, and bring the other table's columns across. You can do it with a lookup formula like XLOOKUP, which updates instantly, or with Power Query's Merge Queries, which joins whole tables and refreshes in one click. This guide shows both on the same small example, plus VLOOKUP for older Excel and fixes for rows that don't match.

What merging two tables means#

Merging, also called joining, puts two tables side by side. A key column links them: a value that appears in both, like an order's customer ID and the ID in a customer list. Each row in the first table gains the matching columns from the second.

That's different from appending, which stacks two tables with the same columns on top of each other, such as January and February sales. If that's what you need, use Power Query's Append Queries or, in Microsoft 365, the VSTACK function.

The example tables#

An orders table in A1:C6 has a customer ID but no customer details. A customer table in E1:G4 has the details:

Customer C-104 is missing from the customer table on purpose, so you can see what happens to a row with no match. The tables can be on different sheets; the guide to VLOOKUP between two sheets shows how the references look then.

Merge two tables with XLOOKUP#

XLOOKUP finds a value in one column and returns the matching value from another. In D2, next to the first order:

=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,"Not found")

It looks for C-102 in the ID column and returns Ben. The fourth argument, "Not found", is shown instead of an #N/A error when a key is missing. Fill it down (Windows: Ctrl+D; Mac: ⌘D); the $ signs keep the customer table fixed as the formula moves. XLOOKUP needs Excel 2021 or Microsoft 365. The XLOOKUP function guide lists every argument, and the article on how to use XLOOKUP covers match modes and errors.

Bring back several columns at once#

XLOOKUP can return more than one column if the return range is wider. To get both Name and Region:

=XLOOKUP(B2,$E$2:$E$4,$F$2:$G$4,"Not found")

The result spills into D2:E2: Ben in D2 and South in E2. Fill it down and the merged table looks like this:

The cells to the right must be empty, or you get a #SPILL! error. For more on returning whole rows, see XLOOKUP with multiple columns.

Merge with VLOOKUP in older Excel#

VLOOKUP does the same job in every version of Excel, one column at a time. For Name, in D2:

=VLOOKUP(B2,$E$2:$G$4,2,FALSE)

The 2 means "return the second column of E:G", and FALSE asks for an exact match. For Region, change the 2 to 3. VLOOKUP shows #N/A for C-104, so wrap it to show your own text: =IFNA(VLOOKUP(B2,$E$2:$G$4,2,FALSE),"Not found"). The key must be the first column of the lookup range, because VLOOKUP only looks to the right. The VLOOKUP function guide explains each argument.

Merge two tables with Power Query#

Power Query joins whole tables without formulas. It is built into Excel 2016 and later on Windows, and into Microsoft 365 on Mac. Follow these steps:

1. Click inside the orders data and choose Insert > Table. On the Table Design tab, name it Orders. Do the same for the customer data and name it Customers.
2. With a cell in Orders selected, choose Data > From Table/Range. In the Power Query Editor, choose Home > Close & Load To, then Only Create Connection. Repeat for Customers.
3. Choose Data > Get Data > Combine Queries > Merge.
4. Pick Orders in the top box and Customers in the bottom box. Click the Customer ID column in the top preview and the ID column in the bottom one.
5. Leave Join Kind as Left Outer (all rows from the first table, matching rows from the second) and click OK.
6. A new column holds the matching customer rows. Click the expand button in its header, tick Name and Region, untick "Use original column name as prefix", and click OK.
7. Choose Home > Close & Load. The merged table appears on a new sheet.

When the orders or customers change, choose Data > Refresh All and the merge runs again. Other join kinds change which rows you keep: Inner keeps only orders with a matching customer, so order 1003 disappears, and Left Anti keeps only the orders with no match, which is a quick way to find problems. New to Power Query? Start with Power Query for beginners.

Formula or Power Query: which to use?#

Both give the same merged table here. Pick by the job:

One key difference: if a customer ID appears twice in the customer table, XLOOKUP and VLOOKUP return only the first match. Power Query returns every match, so an order row can turn into two rows. Check the key column for duplicates before you merge.

Why do some rows not match?#

When a merge shows Not found or #N/A for a key you can see in both tables, the keys aren't exactly the same. Check these:

• Extra spaces. "C-102 " with a trailing space doesn't match "C-102". Clean the column with TRIM.
• Numbers stored as text. An ID of 1001 as a number doesn't match "1001" as text. Convert one side with VALUE, or with B2&"" to turn numbers into text.
• Different formats. "C102" and "C-102" are different keys. Standardise them with SUBSTITUTE first.

To see which keys are missing from either table before you merge, compare the two lists.

Practise it#

Try the exercise on this page: one XLOOKUP brings a region into the orders table. Then practise XLOOKUP in a live spreadsheet and work through the VLOOKUP practice lesson. Both check your formulas as you type, so you'll be ready for the lookup questions in a job test.