Excel guide · 2 min read
XLOOKUP with multiple criteria
=XLOOKUP(1,(A2:A9="North")*(B2:B9="Laptop"),C2:C9). Each condition returns TRUE or FALSE, multiplying them gives 1 only where every condition is true, and XLOOKUP looks for that 1. XLOOKUP needs Excel 365 or Excel 2021 and later.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Product | Month | Sales |
| 2 | North | Laptop | Jan | 14200 |
| 3 | South | Laptop | Jan | 9800 |
| 4 | North | Tablet | Jan | 6100 |
| 5 | North | Laptop | Feb | 15750 |
| 6 | South | Tablet | Feb | 5400 |
| 7 | West | Laptop | Feb | 11300 |
| 8 | South | Laptop | Feb | 10150 |
| 9 | West | Tablet | Jan | 4900 |
Method 1: Multiply the conditions (Boolean logic)
(A2:A9="North") produces a list of TRUE and FALSE values, one per row. When you multiply two of those lists, TRUE counts as 1 and FALSE as 0, so a row only scores 1 if both tests pass.
XLOOKUP then searches that list of 1s and 0s for the value 1 and returns the matching sales figure.
Result: 10150
Returns 10150, the only row that is South, Laptop and Feb.
Add a "not found" value
If no row meets every condition, XLOOKUP returns #N/A. Add a fourth argument to show something friendlier. There is no West Laptop in January, so this returns the message.
Result: No match
Returns "No match".
Method 2: Join the columns together
You can also glue the criteria into one string and look it up in a glued version of the columns. It is easy to read, but add a separator such as "|" so that values cannot accidentally run together and create false matches.
Result: 6100
Returns 6100.
OR logic: match either condition
Use + instead of * to mean "either". Wrap the sum in a test for greater than 0, because a row that meets both conditions would otherwise score 2.
XLOOKUP returns the first match from the top, which here is the North Laptop row.
Result: 14200
Returns 14200, the first row that is either West or a Laptop.
Use cell references, not typed text
In a real sheet, put the criteria in input cells such as G2 and G3 and point the formula at them: =XLOOKUP(1,(A2:A9=G2)*(B2:B9=G3),D2:D9). Now anyone can change the lookup without editing the formula.
If several rows can match and you need all of them, XLOOKUP is the wrong tool. Use FILTER instead, which returns every matching row.
Common mistakes
Using AND() inside the formula
AND collapses the whole range to a single TRUE or FALSE. Multiply the conditions with * instead.
Ranges of different sizes
Every condition range and the return range must cover the same rows, or XLOOKUP returns #VALUE!.
Searching for TRUE instead of 1
Multiplied conditions give numbers, not TRUE. Search for 1.
#NAME? in older Excel
XLOOKUP is not in Excel 2019 or earlier. Use INDEX with MATCH(1,(range1=x)*(range2=y),0) confirmed with Ctrl+Shift+Enter.
FAQ
Can XLOOKUP have multiple criteria?
Not as separate arguments, but you can pass a lookup array built from several conditions. Multiply the conditions and look up 1.
How many criteria can I use?
As many as you like. Keep adding *(range=value) terms. Each one narrows the match further.
How do I get the last match instead of the first?
Set the sixth argument, search_mode, to -1: =XLOOKUP(1,(A2:A9="North")*(B2:B9="Laptop"),D2:D9,"None",0,-1) searches from the bottom up.
What if I want all the matching rows?
Use FILTER: =FILTER(D2:D9,(A2:A9="North")*(B2:B9="Laptop")) spills every matching sales value.
Functions used
XLOOKUPModern lookup in any directionINDEXValue at a positionMATCHPosition of a valueFILTERKeeps matching rows, spills
Related guides
- How to use INDEX MATCH in Excel
- How to return multiple matches in Excel
- VLOOKUP vs XLOOKUP
- Excel IF statement with multiple conditions
- #N/A error in Excel: causes and fixes
Updated