Excel XLOOKUP Multiple Criteria: 2 Easy Ways

Formulas & functions · Updated November 22, 2026

The simplest way to use XLOOKUP with multiple criteria is to combine the lookup values into one value with the & operator and do the same for the lookup array, like this: =XLOOKUP(A2&B2, C2:C100&D2:D100, E2:E100). This works in Excel 2021 and Microsoft 365 and returns the first match where both conditions are true.

Why a Single XLOOKUP Can’t Handle Two Criteria Directly

XLOOKUP is designed to find a single lookup value in a single lookup array. When you have two conditions, like “find the price for Product A in Region North,” there is no built-in syntax for multiple lookup values. The function simply doesn’t accept a range of criteria. So you need to combine the criteria into one searchable value, or use a helper column. This is a common point of confusion because older functions like SUMIFS and COUNTIFS natively accept multiple criteria—but those are aggregate functions, not lookup functions.

Method 1: Concatenate Criteria with & (No Helper Column)

This method is the most direct and doesn’t require modifying your data. The idea is to create a virtual combined key on the fly.

  • Assumptions: Your lookup values are in cells A2 (product) and B2 (region). Your data table has products in C2:C100, regions in D2:D100, and the result you want (e.g., price) in E2:E100.
  • Formula: =XLOOKUP(A2&B2, C2:C100&D2:D100, E2:E100)
  • How it works: The first argument A2&B2 creates a text string like “WidgetNorth”. The second argument C2:C100&D2:D100 creates an array of combined strings like “WidgetNorth”, “GadgetSouth”, etc. XLOOKUP then finds the exact position of “WidgetNorth” in that virtual array and returns the corresponding value from E2:E100.
  • Result: For the example, if row 5 has “Widget” in C5 and “North” in D5, the formula returns the value from E5.

One caution: if your data contains spaces or special characters, you might get false matches. For instance, “AB” and “A B” could collide. To avoid this, add a separator that won’t appear in your data, like =XLOOKUP(A2&"|"&B2, C2:C100&"|"&D2:D100, E2:E100). The pipe symbol (|) is a safe separator because it’s rarely used in text.

Method 2: Use a Helper Column (Easier to Read)

If you prefer a more transparent approach, add a helper column to your data table. This is often easier to audit and can be faster on very large datasets because Excel doesn’t have to build a virtual array each time.

  1. Insert a new column, say column F, next to your data.
  2. In F2, enter =C2&"|"&D2 and copy down for all rows.
  3. Now use a standard XLOOKUP: =XLOOKUP(A2&"|"&B2, F2:F100, E2:E100)

The helper column makes the lookup array a real range, which can be faster and easier to troubleshoot. You can hide the helper column if you don’t want it visible.

What About XLOOKUP with Boolean Logic (TRUE/FALSE)?

Another approach is to use Boolean multiplication inside XLOOKUP, but it requires the use of the FILTER function or an array formula. For example, =XLOOKUP(1, (C2:C100=A2)*(D2:D100=B2), E2:E100) works because the multiplication converts TRUE/FALSE to 1/0, and XLOOKUP finds the first 1. However, this is an array formula that requires Excel 2021 or 365, and it can be slower on large data. The concatenation method is usually simpler and more intuitive.

Common Problems and Solutions

  • #VALUE! error: This often happens if your lookup arrays have different sizes. Ensure C2:C100 and D2:D100 have the same number of rows.
  • #N/A error: No match found. Double-check that the combined criteria exactly match the combined data, including extra spaces. Use TRIM if necessary.
  • False matches due to concatenation: As mentioned, use a separator like "|" to avoid collisions.
  • Case sensitivity: XLOOKUP is case-insensitive by default. If you need case-sensitive matching, use XLOOKUP(..., ..., ..., 2) where the fourth argument (match_mode) is set to 2 for exact match with case sensitivity? Actually, XLOOKUP’s fourth argument is match_mode: 0 for exact match, -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard. There is no case-sensitive option. For case-sensitive, you’d need to use EXACT inside an array formula, which is more complex.

XLOOKUP vs. INDEX/MATCH for Multiple Criteria

Before XLOOKUP, the standard way to do multiple criteria lookups was with INDEX and MATCH using array formulas. For example: =INDEX(E2:E100, MATCH(1, (C2:C100=A2)*(D2:D100=B2), 0)). This still works in all versions of Excel, but it requires pressing Ctrl+Shift+Enter in older versions (pre-dynamic arrays). XLOOKUP is simpler and doesn’t require array entry. However, INDEX/MATCH is more flexible in some ways—for instance, it can return a value from a column to the left of the lookup column, which XLOOKUP can also do with its return_array argument, so that’s not a differentiator. The main advantage of XLOOKUP is readability and the built-in error handling.

Using XLOOKUP with Multiple Criteria and Multiple Results

If you need to return multiple matches, XLOOKUP is not the right tool—it returns only the first match. Use the FILTER function instead. For example, =FILTER(E2:E100, (C2:C100=A2)*(D2:D100=B2)) returns all prices for that product and region. This is available in Excel 2021 and 365. If you need to return the first match but also want to know how many matches exist, you can combine FILTER with COUNTA or use COUNTIFS.

Frequently Asked Questions

Can I use XLOOKUP with two separate lookup columns without concatenating?

No, XLOOKUP requires a single lookup array. Concatenation is the standard workaround. Alternatively, you can use INDEX and MATCH with array logic, but that’s more complex.

Does the order of criteria matter?

No, as long as you concatenate the criteria in the same order in both the lookup value and the lookup array. For example, if you use A2&B2 for the lookup, you must use C2&D2 in the array. If you reverse one, you’ll get no match.

What if my criteria are numbers, not text?

Concatenation works fine with numbers, but be aware that 1&2 becomes the text “12”. If you have numbers like 1 and 2, and another row has 12 and a blank, you could get a false match. Using a separator like "|" solves this: 1&"|"&2 becomes “1|2”, which is unique.

Is there a limit to how many criteria I can use?

You can concatenate as many as you want, but Excel has a limit on formula length (8,192 characters). In practice, more than five or six criteria becomes unwieldy. For many criteria, consider using a helper column that concatenates all of them.

Does this work in Excel for Mac or Excel for the web?

Yes, the same formula syntax works in Excel for Microsoft 365 on Mac and Excel for the web. The only difference is that on Mac, you might use Cmd instead of Ctrl for shortcuts, but the formulas are identical.

Why does my XLOOKUP return #VALUE! even though I have matches?

This usually means the lookup array and return array are not the same size. For example, if you use C2:C100 and D2:D100 but the return array is E2:E99, the sizes differ. Ensure all ranges have the same number of rows.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.