XLOOKUP with Multiple Criteria: Real Examples

XLOOKUP can handle multiple criteria by combining them into a single lookup value with the & operator, then building a matching lookup array the same way. This article shows you the exact formula, how it works, and the pitfalls to avoid.

Formula

The generic formula is:

=XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2), return_range)

Where:

• criteria_range1, criteria_range2 – the columns you want to filter on (e.g., Product, Region).
• criteria1, criteria2 – the values you are looking for.
• return_range – the column from which to return the matching value.

This works because each (range=criteria) returns an array of TRUE/FALSE. Multiplying them with * converts TRUE to 1 and FALSE to 0, and the product is 1 only when all conditions are TRUE. XLOOKUP then finds the first 1 in that array and returns the corresponding value from the return range.

Worked Example

Suppose you have a sales table with columns: A (Product), B (Region), C (Sales). You want the Sales for Product “Widget” in Region “East”.

Enter in a cell:

=XLOOKUP(1, (A:A="Widget") * (B:B="East"), C:C)

Excel evaluates (A:A="Widget") to an array like {TRUE;FALSE;TRUE;…}, and (B:B="East") to another. Multiplying gives {1;0;0;…}. XLOOKUP finds the first 1 and returns the matching sales figure.

Why Use This Instead of INDEX/MATCH?

Before XLOOKUP, the standard multi-criteria formula was {=INDEX(C:C,MATCH(1,(A:A="Widget")*(B:B="East"),0))}, entered as an array formula with Ctrl+Shift+Enter. XLOOKUP does the same job without CSE, and it has a built-in if_not_found argument, so you can return a friendly message instead of #N/A. It also defaults to exact match, which is what you want in most lookups.

Common Mistakes and How to Avoid Them

• Forgetting to lock ranges: When copying the formula down, use absolute references like $A$2:$A$100 to avoid shifting the ranges.
• Mixing data types: If your criteria are numbers stored as text (or vice versa), the comparison fails. Use VALUE() or TEXT() to align types.
• Using whole columns with multiple criteria: Whole-column references (A:A) work but can slow down large workbooks. Limit to the actual data range.
• Not accounting for duplicates: XLOOKUP returns the first match. If you need the last match, use XLOOKUP(1, (range1=crit1)*(range2=crit2), return, , , -1) – the -1 makes it search from the bottom.

Alternatives: Boolean Logic with LET and FILTER

If you have Excel 365, you can use FILTER to return all matches, not just the first. For example, =FILTER(C:C, (A:A="Widget")*(B:B="East")) spills all sales for that product/region. For a single value, you can wrap it with INDEX: =INDEX(FILTER(C:C, (A:A="Widget")*(B:B="East")), 1). The LET function lets you define criteria once, improving readability and performance.

Performance and Large Data

Multi-criteria XLOOKUP with whole-column references can be slow on hundreds of thousands of rows. Using a defined range (e.g., $A$2:$A$100000) helps. Also, consider using a helper column that concatenates the criteria (e.g., =A2&"|"&B2) and then do a simple XLOOKUP on that column. This is often faster because it avoids array multiplication.

Frequently Asked Questions

Can XLOOKUP handle three or more criteria?

Yes, simply multiply more boolean arrays: =XLOOKUP(1, (A:A=crit1)*(B:B=crit2)*(C:C=crit3), D:D). Each additional condition just adds another factor.

Why does my XLOOKUP return #VALUE! instead of a result?

This usually happens when the ranges have different sizes. All criteria ranges and the return range must have the same number of rows. Check that you didn’t accidentally reference a different row count.

How do I use XLOOKUP with OR logic?

Use addition instead of multiplication: ((A:A="Widget")+(A:A="Gadget")) returns 1 if either is true. But be careful: if both are true, the sum is 2, and XLOOKUP won’t find a 1. Use --OR(...) or SIGN() to cap it at 1.

Does XLOOKUP work in older Excel versions?

No, XLOOKUP is only available in Excel 2021 and Microsoft 365. For older versions, use the INDEX/MATCH array formula or SUMPRODUCT.

Can I use XLOOKUP with wildcards for partial matches?

Yes, if you set match_mode to 2 (wildcard). For example, =XLOOKUP("*"&criteria&"*", range, return, , 2). But wildcards only work with text, not with the boolean multiplication method – you’d need to use SEARCH or FIND instead.

What if there are duplicate rows that meet all criteria?

XLOOKUP returns the first occurrence by default. If you need the last, use the search_mode -1. If you need all, use FILTER.

Related

• Print in Place 3D Printer Model Meaning
• Remove GPT Protective Partition? Safe Steps
• Mortal Shell 2: Get Endless Core & Lift Upgrade Cap

in lookup | Lookup value | - | 100 | 0 | 1000000
in col | Lookup column (1=A, 2=B...) | - | 2 | 1 | 100
in exact | Exact match (1=yes, 0=no) | - | 1 | 0 | 1
out result | XLOOKUP result | - | lookup | 0