XLOOKUP With Multiple Criteria in Excel 365
XLOOKUP With Multiple Criteria is a Excel function that xlookup searches for the value 1 in the result of multiplying two condition arrays. Formula Genius generates and validates this formula automatically from a plain-English prompt.
XLOOKUP simplifies multi-criteria lookups in Excel 365. No array formulas, no helper columns — just clean syntax.
The Formula
"Look up a value matching both product name and region"
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")XLOOKUP searches for the value 1 in the result of multiplying two condition arrays. Where both conditions are TRUE (1*1=1), it returns the corresponding value from the result range.
Step-by-Step Breakdown
- (A2:A100=F2) checks each row for a product name match
- (B2:B100=G2) checks each row for a region match
- Multiplying the arrays: 1 only where BOTH conditions match
- XLOOKUP finds the first 1 and returns the value from C2:C100
- "Not found" is the fallback if no match exists
Edge Cases & Warnings
- Requires Excel 365 or Excel 2021 — not available in earlier versions
- Returns the first match when multiple rows satisfy both criteria
- The if_not_found argument eliminates the need for IFERROR wrapping
- Case-insensitive by default
Examples
"Product="Laptop", Region="West""
Returns the value for Laptop in the West region"Product="Keyboard", Region="South" (no match)"
Returns "Not found"Frequently Asked Questions
Is XLOOKUP better than VLOOKUP?
Yes. XLOOKUP handles multiple criteria natively, has a built-in not-found value, can search in any direction, and has cleaner syntax. It's the modern replacement.
Does XLOOKUP work in Google Sheets?
Yes, Google Sheets added XLOOKUP support. The syntax is identical.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.