How to join two tables in Excel
By the Analistable team · Updated · 2 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.
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.