XLOOKUP with Multiple Criteria

To use XLOOKUP with multiple criteria, look up the value 1 against a multiplied boolean array: =XLOOKUP(1, (A2:A100="North")*(B2:B100="Widget"), C2:C100). Each comparison returns TRUE/FALSE, multiplication turns them into 1s and 0s, and XLOOKUP finds the first row where every condition is 1.

How Does the Boolean Multiplication Trick Work?

Multiplying two TRUE/FALSE arrays coerces them to 1/0, so the product is 1 only where both conditions are TRUE. XLOOKUP then searches that array of 1s and 0s for a 1.

Data (A1:C6):
Region   Product   Sales
North    Widget    500
South    Widget    300
North    Gadget    700
South    Gadget    200
North    Widget    450

=XLOOKUP(1, (A2:A6="North")*(B2:B6="Gadget"), C2:C6)
' Result: 700

=XLOOKUP(1, (A2:A6="South")*(B2:B6="Widget"), C2:C6)
' Result: 300

' Three criteria? Just keep multiplying:
=XLOOKUP(1, (A2:A6="North")*(B2:B6="Widget")*(C2:C6>400), C2:C6)
' Result: 500 (first North Widget row over 400)

No Ctrl+Shift+Enter needed — Microsoft 365 handles the array natively.

How Do You Combine Conditions With OR in XLOOKUP?

Add the conditions with + instead of multiplying them, then wrap the sum in >0 and look up TRUE. The wrap matters whenever two conditions can be true for the same row, for example when they test different columns: a row that meets both sums to 2, not 1, so XLOOKUP(1, ...) skips it.

' Region is North OR Product is Widget (same data as above)
=XLOOKUP(1, (A2:A6="North")+(B2:B6="Widget"), C2:C6)
' Result: 300 (wrong: the array is {2;1;1;0;2}, so the first North Widget row is skipped)

=XLOOKUP(TRUE, ((A2:A6="North")+(B2:B6="Widget"))>0, C2:C6)
' Result: 500 (first row where either condition is true)

' Mixed AND/OR: (North OR Widget) AND Sales under 400
=XLOOKUP(1, (((A2:A6="North")+(B2:B6="Widget"))>0)*(C2:C6<400), C2:C6)
' Result: 300

In the mixed version, multiplying the OR group by another condition turns TRUE/FALSE back into 1/0, so the lookup value goes back to 1.

Why Is This Better Than a Helper Column?

The old approach concatenates keys into a helper column (=A2&"|"&B2) and looks up the joined string, which pollutes the sheet, breaks if the delimiter appears in the data, and silently mismatches numbers stored as text. The boolean method compares real values in place, so there is nothing extra to maintain.

You can also build the concatenated key inline, which skips the helper column:

=XLOOKUP("North"&"|"&"Widget", A2:A6&"|"&B2:B6, C2:C6)
' Result: 500

The inline version still breaks if the delimiter appears in the data, and it still converts every key to text before comparing, so the boolean method remains the safer default.

What Is the INDEX MATCH Equivalent?

If you are on Excel 2019 or older without XLOOKUP, the same boolean array works inside MATCH, paired with INDEX.

=INDEX(C2:C6, MATCH(1, (A2:A6="North")*(B2:B6="Gadget"), 0))
' Result: 700
' Excel 2019 and earlier: confirm with Ctrl+Shift+Enter

See INDEX MATCH explained for how the two-function pattern works, and VLOOKUP vs XLOOKUP for why XLOOKUP is the better default when you have it.

What Happens When No Row Matches?

XLOOKUP returns #N/A if no row satisfies every condition; use the built-in if_not_found argument instead of wrapping in IFERROR.

=XLOOKUP(1, (A2:A6="East")*(B2:B6="Widget"), C2:C6, "No match")
' Result: No match

Common Pitfalls

These four issues cause slow workbooks, errors, or unexpected results with the boolean array pattern.

• Whole-column references are slow: (A:A="North")*(B:B="Widget") evaluates a million-row array. Use bounded ranges or a Table.
• All ranges must be the same size: mismatched heights (A2:A100 vs B2:B99) return #VALUE!.
• First match only: XLOOKUP returns one row. To sum every matching row, use SUMIFS or SUMPRODUCT instead.
• Text vs number keys: "2024" stored as text never equals the number 2024, so the product is 0 and you get #N/A.