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.

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.

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

• 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.