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.
=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
- 01(A2:A100=F2) is TRUE or FALSE for each row depending on whether the region matches.
- 02(B2:B100=G2) does the same for the product.
- 03Multiplying the two turns TRUE and FALSE into 1 and 0, so only rows that match both end up as 1.
- 04XLOOKUP looks for the first 1 and returns the value from the same position in C2:C100.
- 05If no row matches both, the formula shows Not found.
The same logic in four tools
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")=ARRAYFORMULA(XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found"))SELECT unit_price
FROM rates
WHERE region = 'West' AND product = 'Widget'
LIMIT 1;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.