Look Up a Value Using Multiple Criteria

Excel & Google Sheets

How it works

The helper-column approach turns a two-criteria problem into a one-criteria problem. In column D, build a key that glues both criteria together: =A2&"|"&B2 gives "West|Stapler". The pipe separator matters — without it, "East"&"1" and "Eas"&"t1" would collide. Then XLOOKUP joins your two search values the same way and finds the combined key in one pass. XLOOKUP needs Excel 365/2021 or Google Sheets; on older Excel, swap in INDEX + MATCH against the same key column. If you'd rather avoid the helper column, the array form =INDEX(C2:C5,MATCH(1,(A2:A5=F2)*(B2:B5=G2),0)) does it in one cell, at the cost of readability.

=A2&"|"&B2 (helper, column D)

Joins the two criteria columns into one unique key per row.

F2&"|"&G2

Builds the same combined key from your two search values — the join must match the helper exactly.

D2:D5

Where to search: the helper key column.

C2:C5

What to return: the value column for the matching row.

When to use it

Use it when rows are only unique in combination: price by region and product, rate by employee and month, stock by warehouse and SKU.

Common mistakes

• ✕Joining without a separator.

  ✓=A2&B2 can produce identical keys from different rows. Always insert a character that never appears in the data: =A2&"|"&B2.

• ✕Building the search key in a different order than the helper.

  ✓If the helper is Region|Product, the lookup must be F2&"|"&G2 in that same order — Product|Region finds nothing and returns #N/A.

• ✕Using XLOOKUP in Excel 2019 or older.

  ✓It shows #NAME?. Use =INDEX(C2:C5,MATCH(F2&"|"&G2,D2:D5,0)) against the same helper column instead.