Analistable

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

Orders (table: Orders)
Order IDCustomer IDAmount
O-9001C-101120
O-9002C-10375
O-9003C-10180
O-9004C-10460
Customers (table: Customers)
Customer IDNameCountry
C-101Bakery LuneFrance
C-102Hart & CoUnited Kingdom
C-103Nordic SupplyDenmark

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

  1. Click in Orders and choose Data → From Table/Range. In the editor choose Close & Load To → Only Create Connection. Do the same for Customers.
  2. Choose Data → Get Data → Combine Queries → Merge.
  3. Select Orders as the first table and Customers as the second.
  4. Click the Customer ID column in both previews.
  5. Choose Left Outer (all from first, matching from second) and click OK. The status bar shows how many rows matched.
  6. In the new Customers column, click the expand icon, tick Name and Country, untick “Use original column name as prefix”, and click OK.
  7. Close & Load.
Result of the left outer join
Order IDCustomer IDAmountNameCountry
O-9001C-101120Bakery LuneFrance
O-9002C-10375Nordic SupplyDenmark
O-9003C-10180Bakery LuneFrance
O-9004C-10460nullnull

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 kinds applied to Orders (first) and Customers (second)
Join kindRows returnedWhat you get
Left Outer4Every order; O-9004 has blank customer fields
Inner3Only orders with a known customer; O-9004 dropped
Full Outer5All 4 orders plus C-102 as a row with no order
Left Anti1Only O-9004 — orders whose customer is missing
Right Anti1Only 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 XLOOKUP or VLOOKUP columns, 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

Common merge problems
SymptomLikely causeFix
Merge dialog says few or no rows matchKey is text in one table and a number in the otherSet both key columns to the same type (Text) in Power Query before merging
More rows after the merge than in OrdersDuplicate Customer IDs in CustomersRemove duplicates on Customer ID in the Customers query, or fix the source
Matches fail for keys that look identicalTrailing spaces or non-breaking spacesTransform → Format → Trim (and Clean) on both key columns
c-101 doesn't match C-101Power Query merges are case-sensitiveFormat → UPPERCASE on both keys, or use a fuzzy merge with case ignored
New orders don't appearThe query wasn't refreshedData → 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.

Related guides