XLOOKUP with Multiple Criteria in Excel

XLOOKUP with multiple criteria lets you look up a value based on two or more conditions (like product name and color) instead of just one. You enter all the criteria as separate lookup arrays using the & operator, and XLOOKUP returns the matching result from the return array. This article explains exactly how to build such formulas, how they work, and common pitfalls to avoid.

How XLOOKUP Works

XLOOKUP is a modern Excel function that searches a range or array for a match and returns a corresponding value from another range. Its syntax is:

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

For a single criterion, you use it like:

=XLOOKUP("Apple", A2:A100, C2:C100)

This finds “Apple” in column A and returns the value from column C in the same row. The function is available in Excel 2021 and Microsoft 365, and it replaces older functions like VLOOKUP and INDEX/MATCH for most lookups.

Why Use Multiple Criteria in XLOOKUP?

Real data often has duplicate values in a single column. For example, a product list might have many rows with the same product name but different sizes or colors. Using only one criterion would return the first match, which may not be the one you want. Multiple criteria narrow the search to a unique combination, such as product name and size and color, so you get the exact record you need.

Common scenarios include:

• Looking up a price based on product and region.
• Finding a score based on student ID and subject.
• Retrieving a shipping cost based on weight and destination.

How to Use XLOOKUP with Multiple Criteria (Step-by-Step)

The core idea is to combine the criteria into a single lookup value and combine the corresponding columns into a single lookup array. You do this with the ampersand (&) operator, which concatenates text or cell references.

1. Identify your criteria columns – For example, column A has product names, column B has colors, and column C has prices.
2. Build the lookup value – Use F2&G2 if your criteria are in cells F2 and G2, or use text directly like "Apple"&"Red".
3. Build the lookup array – Use A2:A100&B2:B100 to combine the two columns into a single array of concatenated values.
4. Set the return array – This is the column that contains the result you want, e.g., C2:C100.
5. Write the formula – The full formula looks like:
   =XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100)

Press Enter and Excel returns the price for the product and color you specified. If no match exists, XLOOKUP returns the #N/A error by default.

Real Example with Sample Data

Suppose you have a table:

A	B	C
Product	Color	Price
Shirt	Blue	15
Shirt	Red	18
Pants	Blue	25
Pants	Red	28

To find the price of a red shirt, you can enter:

=XLOOKUP("Shirt"&"Red", A2:A4&B2:B4, C2:C4)

This returns 18. If you have the criteria in cells E2 and F2, the formula becomes =XLOOKUP(E2&F2, A2:A4&B2:B4, C2:C4).

Using Cell References and Wildcards

Instead of typing text directly, you can reference cells that contain the criteria. This makes the formula dynamic – when you change the criteria cells, the result updates automatically. For example:

=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)

XLOOKUP also supports wildcards if you set match_mode to 2. For instance, to find a product that starts with “Sh” and has color “Red”, you could use "Sh*"&"Red" with match_mode 2. However, wildcards work on the entire lookup value, so combining wildcards with multiple criteria can be tricky because the wildcard applies to the whole concatenated string.

Common Errors and How to Fix Them

Several issues can arise when using XLOOKUP with multiple criteria:

• #VALUE! error – This often happens when the lookup array and return array have different sizes. Make sure both ranges have the same number of rows.
• #N/A error – No match was found. Double-check that your criteria exactly match the data, including spaces and text formatting. Use TRIM to remove extra spaces if needed.
• Incorrect results due to duplicates – If the combined criteria are not unique, XLOOKUP returns the first match. Ensure your criteria uniquely identify a single row.
• Concatenation issues – If your data contains numbers, concatenating with & converts them to text. For example, 1&2 becomes “12”. This can cause mismatches if the data is stored as numbers. To avoid this, use the TEXT function or ensure consistent formatting.

Alternatives to XLOOKUP for Multiple Criteria

If you are using an older version of Excel that does not support XLOOKUP, you can use:

• INDEX and MATCH – Use =INDEX(return_range, MATCH(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), 0)) as an array formula (press Ctrl+Shift+Enter in older versions).
• FILTER function – Available in Excel 365, you can filter the return array by multiple conditions: =FILTER(C2:C100, (A2:A100="Shirt")*(B2:B100="Red")) and take the first result.
• SUMPRODUCT – For numeric returns, =SUMPRODUCT((A2:A100="Shirt")*(B2:B100="Red"), C2:C100) works but only if there is exactly one match.

XLOOKUP is generally simpler and faster than these alternatives, but knowing them helps if you work with legacy files.

Frequently Asked Questions

Can XLOOKUP handle three or more criteria?

Yes, you can concatenate any number of criteria. For example, =XLOOKUP(F2&G2&H2, A2:A100&B2:B100&C2:C100, D2:D100) uses three criteria. Just make sure all ranges have the same length.

Why is my XLOOKUP returning the first match instead of the correct one?

This happens when your combined criteria are not unique. For example, if you only use product and color, but there are multiple rows with the same product and color but different sizes, XLOOKUP returns the first one. Add more criteria to make the combination unique.

Does XLOOKUP work with wildcards in multiple criteria?

Yes, but wildcards apply to the entire concatenated string. You can use "*Shirt*"&"Red" with match_mode 2, but it may match unintended combinations. It is often better to use separate criteria with FILTER or INDEX/MATCH for complex wildcard needs.

Can I use XLOOKUP with multiple criteria across different sheets?

Yes, you can reference ranges from other sheets. For example, =XLOOKUP(F2&G2, Sheet2!A2:A100&Sheet2!B2:B100, Sheet2!C2:C100) works fine.

What if my criteria are numbers and text mixed?

Concatenation converts everything to text. If you have a numeric ID like 123 and you concatenate it with a text criterion, it becomes “123Red”. Ensure the data in the lookup array is stored the same way, or use the TEXT function to format numbers consistently.

Is XLOOKUP case-sensitive?

No, XLOOKUP is not case-sensitive by default. If you need case-sensitive matching, you can use the EXACT function in an array formula, but that is more complex. For most cases, case-insensitivity is fine.

Related

• Rogue Company Won’t Open? Fixes That Work
• Apollo Program: 10 Facts You Didn’t Know
• IP Temporarily Blocked: Troubleshoot & Fix