Lookup formula page
Lookup With Multiple Criteria Formula
Use this pattern when one lookup key is not enough, such as SKU plus region or owner plus status.
Create a lookup formula with multiple criteria.
If H2 is A-100 and H3 is East, the formula returns the matching value from D2:D100.
Copy formulas
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")The XLOOKUP formula combines two Boolean criteria with multiplication, so both conditions must be TRUE before the return value is selected.
Example data
| SKU | Region | Product | Price |
|---|---|---|---|
| A-100 | East | Keyboard | 49 |
| A-100 | West | Keyboard | 52 |
| A-101 | East | Mouse | 25 |
If H2 is A-100 and H3 is East, the formula returns the matching value from D2:D100.
How the formula works
- Each condition creates a TRUE/FALSE array.
- Multiplying the arrays creates 1 only when every condition matches.
- XLOOKUP searches for 1 and returns the aligned result.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100=H2 | The first Boolean array comparing each key with H2. |
| B2:B100=H3 | The second Boolean array comparing each region with H3. |
| * | Multiplication converts the two Boolean arrays into an AND condition with 1 for a match. |
| D2:D100 | The return range containing the value for the first matching combination. |
Verified examples
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Excel: Enter A-100 in H2, East in H3, and use the sample key, region, and price columns. Returns: 49
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Google Sheets: Enter A-100 in H2 and West in H3 with the sample table. Returns: 52
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Excel: Enter A-101 in H2 and West in H3; that combination is absent. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The formula returns #VALUE! | A criteria array and the return range have different sizes. | Make both criteria ranges and D2:D100 use the same starting and ending rows. |
| A visible combination is not found | The key or region contains different spacing, spelling, or text types. | Normalize both criteria cells and the source columns before matching. |
| A duplicate combination returns one result | XLOOKUP returns the first row where the multiplied criteria equal 1. | Remove duplicate combinations or use FILTER when all matches are required. |
When not to use this formula
- Do not use this single-result XLOOKUP when multiple rows for the same criteria combination must be returned.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Multiple Criteria Example | Use the worked example when the criteria need to be adapted to a second field. |
| INDEX MATCH Multiple Criteria Example | Use the INDEX MATCH pattern when XLOOKUP is unavailable. |
Related formulas
Official references
- XLOOKUP function from Microsoft
FAQ
Why does multiplication mean AND here?
Each comparison produces TRUE or FALSE; multiplication converts both TRUE values to 1 only when both criteria match the same row.
What happens with duplicate combinations?
XLOOKUP returns the first row where both criteria are true. Use FILTER to return every matching row.