Formula example
XLOOKUP Multiple Criteria Formula Example
Return the price where SKU is A-100 and Region is West. With the sample data, the formula returns 52.
Copyable formula
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Returns 52 when H2 is A-100 and H3 is West.
Useful variations
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Use this when the two lookup values are typed in helper cells H2 and H3.
=XLOOKUP(1, (A2:A100="A-100")*(B2:B100="West"), D2:D100, "Not found")Use quoted text criteria when the lookup values are fixed inside the formula.
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), C2:C100, "Not found")Change only the return range when the matching row is the same but the returned column is different.
Sample data
| SKU | Region | Product | Price |
|---|---|---|---|
| A-100 | East | Keyboard | 49 |
| A-100 | West | Keyboard | 52 |
| A-101 | East | Mouse | 25 |
When to use this formula
- One lookup value is not enough to identify the correct row.
- You need to match a SKU plus region, employee plus month, or customer plus product.
- You want one returned value and a clear Not found message when no row matches.
Why the formula searches for 1
The first condition creates TRUE values where SKU equals H2. The second condition creates TRUE values where Region equals H3. Multiplying the two arrays converts TRUE to 1 and FALSE to 0, so only rows that meet both criteria become 1.
Excel and Google Sheets support
Excel 365 and current Google Sheets can evaluate this XLOOKUP array pattern directly. Older desktop Excel versions that do not support dynamic arrays should use INDEX MATCH with multiple criteria instead.
How to troubleshoot Not found
If the formula returns Not found, check spelling, extra spaces, number-as-text values, and whether each criteria range has the same height as the return range. The formula returns the first matching row, so sort or deduplicate the source table if later matches should win.
Returned results from the sample data
| Criteria | Matching row | Returned value |
|---|---|---|
| SKU A-100 and Region East | A-100 | East | Keyboard | 49 | 49 |
| SKU A-100 and Region West | A-100 | West | Keyboard | 52 | 52 |
| SKU A-101 and Region East | A-101 | East | Mouse | 25 | 25 |
Alternative formulas
| Need | Use | Why |
|---|---|---|
| Return one match by two or more columns | XLOOKUP multiple criteria | Readable in Excel 365 and Google Sheets |
| Support older Excel files | INDEX MATCH multiple criteria | Works as an array formula in older Excel |
| Return several matching rows | FILTER | Spills every row that meets the criteria |
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
- Keep every condition range and the return range the same height.
- Use exact text values, including spaces and hyphens, for SKU or customer IDs.
- Remember that XLOOKUP returns the first matching row when duplicates exist.
- Use INDEX MATCH with multiple criteria when the workbook must support older Excel versions.
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 XLOOKUP multiple criteria pattern?
Yes. Current Google Sheets supports XLOOKUP and array comparisons, so the same multiply-the-conditions pattern works there.
Why does the formula search for 1?
Each matching condition becomes TRUE, and TRUE multiplied by TRUE becomes 1. Rows that fail either condition become 0.
What happens with duplicate matches?
XLOOKUP returns the first matching row by default. If duplicates are valid, sort the source table first or use FILTER to return all matching rows.
What should I use in older Excel versions?
Use INDEX MATCH with multiple criteria in Excel versions that do not support dynamic arrays or modern XLOOKUP behavior.