Cellwise

Excel guide · 2 min read

XLOOKUP with multiple criteria

Use =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 formula on this page was calculated live against this sheet.

ABCD
1RegionProductMonthSales
2NorthLaptopJan14200
3SouthLaptopJan9800
4NorthTabletJan6100
5NorthLaptopFeb15750
6SouthTabletFeb5400
7WestLaptopFeb11300
8SouthLaptopFeb10150
9WestTabletJan4900

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.

fx=XLOOKUP(1,(A2:A9="South")*(B2:B9="Laptop")*(C2:C9="Feb"),D2:D9)

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.

fx=XLOOKUP(1,(A2:A9="West")*(B2:B9="Laptop")*(C2:C9="Jan"),D2:D9,"No match")

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.

fx=XLOOKUP("North|Tablet",A2:A9&"|"&B2:B9,D2:D9)

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.

fx=XLOOKUP(1,--(((A2:A9="West")+(B2:B9="Laptop"))>0),D2:D9)

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

Keep learning

Updated 2026-10-31