One file has your customers. Another has their orders, or a price list has codes and a stock report has the same codes with quantities. You want one table with everything side by side, lined up by the column they share. That is a merge (a "join" in database terms), and Excel can do it in two ways: a lookup formula, or Power Query. If you would rather not write either, you can do it in your browser.
Do you need to join the files or stack them?
"Merge" means two different jobs. Joining lines up rows from both files by a shared value, adding columns: customer ID 1042 gets its email from one file and its order total from the other. Stacking puts one file's rows under the other's, for example January's sales above February's. This guide is about joining. To stack, see the FAQ at the end.
What do you need before merging?
- A key column in both files whose values identify a row: an ID, an email address, a product code. Names make poor keys because people share them.
- The same kind of value on both sides.
1042as a number does not match"1042"as text. - No stray spaces.
"ACME "does not match"ACME". - Unique keys in the file you look up from. If a key appears twice, a lookup only uses the first one.
Most failed merges come from the second and third points. VLOOKUP not working? shows how to spot and fix them.
How do you merge two Excel files with XLOOKUP?
- Open both files and copy the second table into a new sheet of the first workbook. Call it
Orders. (You can look up into another open workbook, but one workbook is easier to keep working.) - In your main sheet, add a heading for the column you want to bring across, say
Totalin column D. - In
D2, type the lookup. Here the key is in column A of both sheets and the total is in column C ofOrders:
=XLOOKUP(A2,Orders!$A$2:$A$500,Orders!$C$2:$C$500,"Not found") - A2: lookup_value
- Orders!$A$2:$A$500: lookup_array
- Orders!$C$2:$C$500: return_array
- "Not found": if_not_found
- Double-click the fill handle (the small square at the bottom right of the cell) to copy the formula down. The
$signs keep the ranges fixed whileA2moves down. - Need several columns? Make the return range wider and they spill across in one go:
=XLOOKUP(A2,Orders!$A$2:$A$500,Orders!$B$2:$D$500,"Not found") To freeze the result, copy the merged columns and use Paste Special, Values. In Excel 2019 or earlier, use VLOOKUP instead; the key must then be the first column of the range:
=IFNA(VLOOKUP(A2,Orders!$A$2:$D$500,3,FALSE),"Not found") XLOOKUP vs VLOOKUP explains the differences. To practise the formula, try XLOOKUP basics and Looking up from another sheet.
How do you merge on two columns, like first and last name?
Join the key columns together on both sides and look that up. The "|" separator stops "Ann" & "abel" matching "Anna" & "bel":
=XLOOKUP(A2&"|"&B2,Orders!$A$2:$A$500&"|"&Orders!$B$2:$B$500,Orders!$D$2:$D$500,"Not found") This works in Excel 365 and 2021 or later. In older versions, add a helper column that joins the keys in each table, and look up that column.
How do you merge two Excel files with Power Query?
Power Query is built into Excel 2016 and later and is the better choice for big files or a merge you repeat every month:
- Go to Data, Get Data, From File, From Workbook, pick the first file and load its table as a query. Do the same for the second file.
- Select the first query and choose Home, Merge Queries.
- Pick the second table, click the key column in each (hold Ctrl to choose two columns), and choose a join kind.
- Click the expand icon on the new column header, tick the columns you want, then Close & Load.
The join kind decides which rows you get back:
| Join kind | You get | Same as |
|---|---|---|
| Left Outer | Every row of the first file, with matches filled in | A lookup formula copied down |
| Inner | Only rows found in both files | Filtering out the "Not found" rows |
| Left Anti | Rows of the first file with no match | A list of what's missing |
| Full Outer | Every row from both files | Both lookups plus both missing lists |
Next month, drop the new files in the same place and click Refresh.
What happens to rows that don't match?
With the formulas above they show "Not found", which is what you want: the rows stay lined up with your original list, and you can see exactly which
ones need attention. Before assuming the record really is missing, check the usual suspects: numbers stored as text, trailing spaces and leading zeros
(00421 vs 421). To see the missing rows from the other side too, compare the files in both directions as shown in how to compare two Excel sheets.
What about duplicate keys?
A lookup formula returns the first match and ignores the rest. Power Query returns every match, so a customer with three orders becomes three rows. Count
how often each key appears with =COUNTIF(Orders!$A$2:$A$500,A2) before you merge, so neither result surprises you.
How can you merge two files without formulas?
Open Lookup & Merge, drop in both files (.xlsx, .xls or .csv), choose the column to match on
and the columns to bring back. You can:
- keep every row, keep only matches, or list what's missing from each side (the Left Outer, Inner and Anti joins above);
- match on two or more columns at once, such as first name and last name;
- ignore case, extra spaces, punctuation and leading zeros, or turn on fuzzy matching for near-misses like "Acme Ltd" and "ACME Limited";
- pick the first, last or every match when a key is duplicated.
Every row that doesn't match tells you why, and the result downloads as .xlsx or .csv. It also shows the XLOOKUP formula for your exact columns, so you can repeat the merge in Excel next time. The files are read in your browser and never
uploaded. Need to tidy a file before or after merging? Load it into the Table Workspace to remove duplicates,
trim spaces or reorder columns.