# How to merge two tables by a common ID in Excel

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"))`.

**Excel:** `=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=HSTACK(A2:B7,ARRAYFORMULA(IFNA(VLOOKUP(A2:A7,D2:E6,2,FALSE),"Missing")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee ID | Name |  | Employee ID | Department |  | Merged |
| E-501 | Lena Park |  | E-504 | Sales |  |  |
| E-502 | Omar Ruiz |  | E-501 | Finance |  |  |
| E-503 | Noor Haddad |  | E-506 | IT |  |  |
| E-504 | Tom Becker |  | E-502 | Sales |  |  |
| E-505 | Priya Shah |  | E-503 | IT |  |  |
| E-506 | Ines Lopez |  |  |  |  |  |

Formula in G2: `=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"))`

Result (spills 6 × 3): E-501, Lena Park, Finance; E-502, Omar Ruiz, Sales; E-503, Noor Haddad, IT; E-504, Tom Becker, Sales; E-505, Priya Shah, Missing; E-506, Ines Lopez, IT

## 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.

## Other ways

- `=XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6,"Missing")`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/merge-two-tables-by-id