Formula example
XLOOKUP with Multiple Criteria
You need to return a price where SKU matches H2 and Region matches H3.
Copyable formula
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Sample data
| SKU | Region | Product | Price |
|---|---|---|---|
| A-100 | East | Keyboard | 49 |
| A-100 | West | Keyboard | 52 |
| A-101 | East | Mouse | 25 |
Formula explanation
- Each condition returns TRUE or FALSE for each row.
- Multiplying the conditions creates 1 only when both criteria match.
- XLOOKUP searches for 1 and returns the aligned value from the return range.
Common errors
- The condition ranges must be the same size.
- Older Excel versions may need Ctrl+Shift+Enter or an INDEX MATCH alternative.
- Check that duplicate matches do not hide later valid rows.
Build your own version
Use the XLOOKUP builder and switch Lookup mode to Multiple criteria to generate this pattern. XLOOKUP Formula Builder.
Related formulas
FAQ
Can Google Sheets use this pattern?
Yes, Google Sheets supports array comparisons in XLOOKUP-style formulas.
What happens with duplicates?
XLOOKUP returns the first matching row by default.