XLOOKUP With Multiple Criteria

XLOOKUP with multiple criteria works by combining the conditions into one lookup. You either join the values with &, or multiply TRUE/FALSE tests and look up the number 1. Both find the first row where every condition is true, such as the price of a Hoodie in size M. This guide shows each method step by step on a small price list, when to use which, and how to handle no match.

Why XLOOKUP needs a trick for multiple criteria#

XLOOKUP looks for one value in one list. Its syntax has no slot for a second condition:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

When the item you want is defined by two columns, neither column alone is enough. In this price list, "Hoodie" appears three times and "M" appears twice, but "Hoodie and M" appears once:

A plain =XLOOKUP("Hoodie",A2:A8,C2:C8) returns 30, the first Hoodie, whatever the size. The XLOOKUP guide covers the single-condition basics, and the XLOOKUP function guide lists every argument.

All examples below have the product to find in G2 (Hoodie) and the size in H2 (M).

Method 1: Join the criteria with &#

The simplest method joins the criteria into one text value, and joins the columns the same way:

=XLOOKUP(G2&"|"&H2,A2:A8&"|"&B2:B8,C2:C8)

It returns 32. Here is how it works:

• G2&"|"&H2 becomes the single value Hoodie|M.
• A2:A8&"|"&B2:B8 builds a list on the fly: T-shirt|S, T-shirt|M, T-shirt|L, Hoodie|S, Hoodie|M, and so on.
• XLOOKUP finds Hoodie|M in position 5 and returns the 5th price, 32.

The | in the middle is a separator. Without it, different pairs can join into the same text: "AB" plus "C" and "A" plus "BC" both become "ABC". Pick any character that never appears in your data.

This method is easy to read and works for exact matches. It turns numbers into text as it joins them, which is fine as long as you join both sides the same way.

Method 2: Multiply TRUE/FALSE tests and look up 1#

The second method tests each condition separately, then combines the results:

=XLOOKUP(1,(A2:A8=G2)*(B2:B8=H2),C2:C8)

It also returns 32. Step by step:

1. (A2:A8=G2) checks every product against Hoodie and gives a list of TRUE and FALSE: FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE.
2. (B2:B8=H2) checks every size against M: FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE.
3. Multiplying them turns TRUE into 1 and FALSE into 0, and gives 1 only where both are true: 0, 0, 0, 0, 1, 0, 0.
4. XLOOKUP looks for 1 in that list, finds it in position 5 and returns 32.

The brackets around each test are required. Add a third condition by multiplying again: (A2:A8=G2)*(B2:B8=H2)*(C2:C8>20).

Which method should you use?#

Both give the same answer for exact matches. Choose by the kind of conditions you have:

Use the join method when every condition is "equals". Use the multiply method when any condition compares sizes, dates or amounts. For example, the first Hoodie that costs more than 31:

=XLOOKUP(1,(A2:A8="Hoodie")*(C2:C8>31),B2:B8)

This returns M, the size of the first row that passes both tests.

Handle no match and return more than one column#

If no row meets every condition, XLOOKUP returns #N/A. Use the 4th argument to show a message instead:

=XLOOKUP(G2&"|"&H2,A2:A8&"|"&B2:B8,C2:C8,"Not found")

With Cap and M in G2 and H2, this shows Not found. The guide to XLOOKUP if not found has more options.

To bring back several columns at once, make the return array wider. If the list had a Stock column in D, C2:D8 as the return array would spill the price and the stock side by side. See XLOOKUP with multiple columns for this. The same formulas also work when the price list is on another sheet; just include the sheet name in each range, as shown in XLOOKUP from another sheet.

OR logic, last match and older versions of Excel#

A few variations cover most remaining cases:

• OR logic. Add the tests instead of multiplying them, then check for more than 0: =XLOOKUP(1,--((A2:A8="Cap")+(B2:B8="L")>0),C2:C8) returns 15, the first row that is a Cap or size L.
• Last match. Set the 6th argument to -1 to search from the bottom up: =XLOOKUP(1,(A2:A8="Hoodie")*(C2:C8<40),C2:C8,,0,-1) returns 35.
• Older Excel. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. In older versions, use INDEX and MATCH with the same multiply logic: =INDEX(C2:C8,MATCH(1,(A2:A8=G2)*(B2:B8=H2),0)). In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter (on a Mac, Control+Shift+Return or ⌘+Shift+Return). The guide to lookups with multiple criteria compares every method, including a helper column.

Common mistakes with XLOOKUP and multiple criteria#

These cause most #N/A results and wrong answers:

1. Joining only one side. If you join G2 and H2 but not the columns, nothing matches. Join both the same way, with the same separator.
2. Ranges of different sizes. A2:A8 with B2:B9 returns #VALUE!. Keep every range on the same rows.
3. Hidden spaces. "Hoodie " with a trailing space doesn't equal "Hoodie". Clean the data with TRIM.
4. Missing brackets around tests. A2:A8=G2*B2:B8=H2 is read in the wrong order. Write (A2:A8=G2)*(B2:B8=H2).
5. Numbers vs text. A size typed as the number 10 doesn't equal the text "10". Make both sides the same type.

Practise it#

The join method uses two skills at once: XLOOKUP and joining text. Practise XLOOKUP in a live spreadsheet: you look up in any direction and add a friendly not-found message, and each formula is checked instantly. Then build joined keys with & in the joining text lesson. Start with the Hoodie price in the exercise on this page.