How to look up a value in two tables in Excel

Q: 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")).

Formula (Excel and Google Sheets)

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

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

Result on the example: $44.50 in H2
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.

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

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

Result44.5

H1"CL-207"
The value in H1.
A2:A65 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …
A range: 5 cells in one column.
B2:B65 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
A range: 5 cells in one column.
D2:D54 rows × 1 col: "CL-201", "CL-204", "CL-207", "CL-210"
A range: 4 cells in one column.
E2:E54 rows × 1 col: $19.99, $129.00, $44.50, $24.00
A range: 4 cells in one column.
XLOOKUP(H1,D2:D5,E2:E5,"Not found")44.5
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueH1"CL-207"
- lookup_arrayD2:D54 rows × 1 col: "CL-201", "CL-204", "CL-207", "CL-210"
- return_arrayE2:E54 rows × 1 col: $19.99, $129.00, $44.50, $24.00
- if_not_found"Not found"
XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))44.5
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueH1"CL-207"
- lookup_arrayA2:A65 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …
- return_arrayB2:B65 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
- if_not_foundXLOOKUP(H1,D2:D5,E2:E5,"Not found")44.5

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found")). We ran it in Google Sheets mode on the same data and it returns $44.50.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IFNA(VLOOKUP(H1,A2:B6,2,FALSE),IFNA(VLOOKUP(H1,D2:E5,2,FALSE),"Not found"))	$44.50	The same chain with IFNA and VLOOKUP, for every version.
=XLOOKUP(H1,VSTACK(A2:A6,D2:D5),VSTACK(B2:B6,E2:E5),"Not found")	$44.50	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.

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.

Look up a value in two tables

Prices are split between the regular list and the clearance list. In H2, return the price for the SKU in H1 from whichever list has it, or "Not found".

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.

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.