How to join two tables in Excel
By the Analistable team · Updated · 5 min read
To combine two tables that share a key column, load both into Power Query, choose Home → Merge Queries, click the key column in each table, pick a Join Kind (Left Outer keeps every row of the first table) and expand the columns you need. For one or two columns, an XLOOKUP column does the same job without Power Query.
Part of our guide: How to join spreadsheets on a common column
The two tables
| Order ID | Customer ID | Amount |
|---|---|---|
| O-9001 | C-101 | 120 |
| O-9002 | C-103 | 75 |
| O-9003 | C-101 | 80 |
| O-9004 | C-104 | 60 |
| Customer ID | Name | Country |
|---|---|---|
| C-101 | Bakery Lune | France |
| C-102 | Hart & Co | United Kingdom |
| C-103 | Nordic Supply | Denmark |
We want each order with its customer's name and country. Note C-104 has no customer record and C-102 has no orders — the join kind decides what happens to them.
Option 1: Power Query Merge Queries
- Click in Orders and choose Data → From Table/Range. In the editor choose Close & Load To → Only Create Connection. Do the same for Customers.
- Choose Data → Get Data → Combine Queries → Merge.
- Select Orders as the first table and Customers as the second.
- Click the Customer ID column in both previews.
- Choose Left Outer (all from first, matching from second) and click OK. The status bar shows how many rows matched.
- In the new Customers column, click the expand icon, tick Name and Country, untick “Use original column name as prefix”, and click OK.
- Close & Load.
| Order ID | Customer ID | Amount | Name | Country |
|---|---|---|---|---|
| O-9001 | C-101 | 120 | Bakery Lune | France |
| O-9002 | C-103 | 75 | Nordic Supply | Denmark |
| O-9003 | C-101 | 80 | Bakery Lune | France |
| O-9004 | C-104 | 60 | null | null |
Order O-9004 keeps its row with empty customer fields, which flags a missing customer record. An Inner join would drop it — see inner join in Excel.
Option 2: XLOOKUP columns
Add columns to the Orders table:
=XLOOKUP([@[Customer ID]], Customers[Customer ID], Customers[Name], "Not found")XLOOKUP works when each order has at most one customer (many-to-one). If the second table can have several matches per key, XLOOKUP returns only the first; use Merge Queries, which returns one row per match.
Check the join
- Row count: a left join from Orders should return exactly as many rows as Orders (4 here). More rows means duplicate Customer IDs in Customers.
- Unmatched rows: filter the Name column for null to see orders with no customer.
- Keys: if few rows match, check for spaces or text-vs-number differences in the key columns.
Background on join kinds and when to use a lookup instead: joining spreadsheets on a common column.
Which join kind to pick for these two tables
| Join kind | Rows returned | What you get |
|---|---|---|
| Left Outer | 4 | Every order; O-9004 has blank customer fields |
| Inner | 3 | Only orders with a known customer; O-9004 dropped |
| Full Outer | 5 | All 4 orders plus C-102 as a row with no order |
| Left Anti | 1 | Only O-9004 — orders whose customer is missing |
| Right Anti | 1 | Only C-102 — customers with no orders |
For enriching a transaction table, Left Outer is the safe default because it never loses an order. Run a Left Anti merge alongside it as a data-quality check: if it returns rows, someone needs to add those customers to the master list. Every join kind is covered in Power Query Merge Queries join kinds.
Excel versions and Mac
- Excel 365 and Excel 2021/2024 for Windows: Merge is under Data → Get Data → Combine Queries → Merge, or Home → Merge Queries inside the Power Query editor.
- Excel 2016 and 2019 for Windows: Power Query is built in under Data → Get & Transform; the Merge dialog and join kinds are the same.
- Excel 2010 and 2013: Power Query is a separate free add-in from Microsoft, with a Power Query ribbon tab.
- Excel for Mac (Microsoft 365): recent builds include the Power Query editor, but older Mac versions have limited Power Query support. If Merge is missing, use the XLOOKUP method or see combining Excel files on a Mac.
- Google Sheets has no Merge Queries; use
XLOOKUPorVLOOKUPcolumns, or the Google Sheets guide to combining data.
Bringing several columns with one XLOOKUP
In Microsoft 365, XLOOKUP can return several adjacent columns at once if the return range spans them. In a cell to the right of the Orders data (outside the table, because tables can't hold spilled formulas), enter for the first order and fill down:
=XLOOKUP(B2, Customers[Customer ID], Customers[[Name]:[Country]], "Not found")Each formula spills Name and Country across two cells. Leave the cell to the right empty, or you'll get a #SPILL! error. In Excel 2019 and earlier, use one INDEX/MATCH per column instead. For the trade-offs between lookup functions, see VLOOKUP vs XLOOKUP vs INDEX MATCH.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Merge dialog says few or no rows match | Key is text in one table and a number in the other | Set both key columns to the same type (Text) in Power Query before merging |
| More rows after the merge than in Orders | Duplicate Customer IDs in Customers | Remove duplicates on Customer ID in the Customers query, or fix the source |
| Matches fail for keys that look identical | Trailing spaces or non-breaking spaces | Transform → Format → Trim (and Clean) on both key columns |
| c-101 doesn't match C-101 | Power Query merges are case-sensitive | Format → UPPERCASE on both keys, or use a fuzzy merge with case ignored |
| New orders don't appear | The query wasn't refreshed | Data → Refresh All after adding rows to the source tables |
Repeating the join every month
Because both inputs are Excel tables, new rows pasted at the bottom extend the table automatically. Click Data → Refresh All and the merged sheet updates with the same join kind and expanded columns. If next month's data arrives as separate files instead, load them through Power Query's From Folder first and merge the combined query with Customers.
Frequently asked questions
- How do I merge two tables in Excel based on one column?
- Use Data → Get Data → Combine Queries → Merge, select the shared column in both tables, choose a join kind and expand the columns you want.
- What's the difference between Merge and Append in Excel?
- Merge adds columns from another table by matching a key. Append adds rows from another table with the same columns.
- Can I join two tables without Power Query?
- Yes, with XLOOKUP or INDEX/MATCH columns, as long as each row has at most one match in the other table.
- Why does my merged table have more rows than the original?
- The second table has the same key more than once, so each matching row is returned. Remove duplicates on the key in the second table if each key should appear only once.