SheetSense

XLOOKUP with multiple criteria

Multiplying two comparisons gives 1 only on rows where both conditions are true, and XLOOKUP searches that result for the first 1.

fx
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

The sheet: A rates table has Region in column A, Product in column B and Unit price in column C. F2 and G2 hold the region and product you want the price for.

How it works, step by step

  1. 01(A2:A100=F2) is TRUE or FALSE for each row depending on whether the region matches.
  2. 02(B2:B100=G2) does the same for the product.
  3. 03Multiplying the two turns TRUE and FALSE into 1 and 0, so only rows that match both end up as 1.
  4. 04XLOOKUP looks for the first 1 and returns the value from the same position in C2:C100.
  5. 05If no row matches both, the formula shows Not found.

The same logic in four tools

Excel
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
Google Sheets
=ARRAYFORMULA(XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found"))
SQL
SELECT unit_price
FROM rates
WHERE region = 'West' AND product = 'Widget'
LIMIT 1;
Python (pandas)
match = rates[(rates["region"] == "West") & (rates["product"] == "Widget")]
price = match["unit_price"].iloc[0] if not match.empty else "Not found"

Mistakes that break it

  • !Only the first matching row is returned. If two rows match both criteria, the second is ignored.
  • !XLOOKUP needs Excel 2021 or Microsoft 365. Older versions need INDEX and MATCH.
  • !All ranges must be the same size or the formula returns an error.

Common questions

Can I use more than two criteria?
Yes. Multiply one more comparison in, for example (C2:C100=H2), for each extra condition.
What about OR logic?
Add the comparisons instead of multiplying them and wrap the sum in a test such as (A2:A100=F2)+(B2:B100=G2)>0.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.