# How to look up a value in two tables in Excel

Search the second table when the first has no match, using XLOOKUP's if_not_found argument: `=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))`.

**Excel:** `=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| SKU | Price |  | Clearance SKU | Price |  | SKU | CL-207 |
| P-101 | 39.99 |  | CL-201 | 19.99 |  | Price |  |
| P-102 | 249 |  | CL-204 | 129 |  |  |  |
| P-103 | 89.5 |  | CL-207 | 44.5 |  |  |  |
| P-104 | 449 |  | CL-210 | 24 |  |  |  |
| P-105 | 74.95 |  |  |  |  |  |  |

Formula in H2: `=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))`

Result: $44.50

## How it works

The outer XLOOKUP searches the regular list. When the SKU isn't there, it returns its 4th argument, which is itself a lookup in the clearance list. CL-207 is found there, $44.50. Only a SKU missing from both lists shows "Not found".

## Other ways

- `=IFNA(VLOOKUP(H1,A2:B6,2,FALSE),IFNA(VLOOKUP(H1,D2:E5,2,FALSE),"Not found"))`: The same chain with IFNA and VLOOKUP, for every version.
- `=XLOOKUP(H1,VSTACK(A2:A6,D2:D5),VSTACK(B2:B6,E2:E5),"Not found")`: Stacks both lists into one and searches it once. Easiest when there are several tables. Excel 2024 and Microsoft 365.

## Common mistakes

- If a SKU is in both lists, the first table wins. Put the table that should take priority first.
- IFERROR in place of IFNA would also hide real errors in the first lookup, such as a wrong range.

## Questions

**How do I show which list the price came from?**

Use a second formula: `=IF(COUNTIF(A2:A6,H1),"Regular",IF(COUNTIF(D2:D5,H1),"Clearance","Not found"))`.

**Can I search tables on different sheets?**

Yes, the same formula works with sheet references in the ranges.

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/lookup-in-two-tables