XLOOKUP with Multiple Criteria
XLOOKUP accepts one lookup value. For multiple criteria, use a helper column, Boolean arrays, or FILTER.
Helper column (recommended for large data)
Helper in D2: =A2&"|"&B2
Lookup: =XLOOKUP(F2&"|"&G2, D:D, C:C, "Not found")Array Boolean (Microsoft 365)
=XLOOKUP(1, (A2:A1000=F2)*(B2:B1000=G2), C2:C1000, "Not found")FILTER when multiple rows can match
=FILTER(C2:C1000, (A2:A1000=F2)*(B2:B1000=G2), "No rows")Clear spill space or you will hit #SPILL!.
Errors to watch
- #N/A → no row matches; set if_not_found
- #VALUE! → mismatched range sizes in Boolean arrays
- Text vs number keys → coerce with -- or VALUE
XLOOKUP not found · Error directory