How to merge two tables by a common ID in Excel
Excel formula
=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"))Keep the first table and add the matching column from the second with one lookup: =HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing")).
- Result on the example
- a 6-row list that spills from G2 in
G2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP with the whole ID column A2:A7 as its lookup value finds each ID in the second table and returns one department per row, in the first table's order. HSTACK puts those departments next to the ID and name columns. E-505 has no department row, so it shows "Missing" instead of an error.
=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"))Result{6 rows × 3 cols} "E-501", "Lena Park", "Finance", "E-502", …
A2:B76 rows × 2 cols: "E-501", "Lena Park", "E-502", "Omar Ruiz", …A range: 6 rows × 2 columns.
A2:A76 rows × 1 col: "E-501", "E-502", "E-503", "E-504", …A range: 6 cells in one column.
D2:D65 rows × 1 col: "E-504", "E-501", "E-506", "E-502", …A range: 5 cells in one column.
E2:E65 rows × 1 col: "Sales", "Finance", "IT", "Sales", …A range: 5 cells in one column.
XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"){6 rows × 1 col} "Finance", "Sales", "IT", "Sales", …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2:A76 rows × 1 col: "E-501", "E-502", "E-503", "E-504", … - lookup_array
D2:D65 rows × 1 col: "E-504", "E-501", "E-506", "E-502", … - return_array
E2:E65 rows × 1 col: "Sales", "Finance", "IT", "Sales", … - if_not_found
"Missing"
- lookup_value
HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing")){6 rows × 3 cols} "E-501", "Lena Park", "Finance", "E-502", …HSTACK: Places ranges side by side.- array1
A2:B76 rows × 2 cols: "E-501", "Lena Park", "E-502", "Omar Ruiz", … - array2
XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"){6 rows × 1 col} "Finance", "Sales", "IT", "Sales", …
- array1
In Google Sheets
Google Sheets needs a different formula: =HSTACK(A2:B7,ARRAYFORMULA(IFNA(VLOOKUP(A2:A7,D2:E6,2,FALSE),"Missing"))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6,"Missing") | FinanceDifferent result | For Excel 2021 and earlier: put this next to the first table and fill it down, one department per row. One department in one cell. |
Common mistakes
- IDs must match exactly in both tables: "E-501" and "E501", or a number and the same number stored as text, don't join.
- If an ID appears twice in the second table, only its first row is used.
- This keeps every row of the first table, like a left join. Rows only in the second table are left out.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Merge two tables by a common ID
G2, return one table with the ID, name and department for every employee in the first table.Questions
How do I bring over several columns?
Add one XLOOKUP per column inside HSTACK, or return a wider range when you fill a formula down row by row.
Is there a non-formula way?
Power Query (Data > Get Data) has a Merge Queries command that joins two tables by a key column and refreshes on demand.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.