XLOOKUP Multiple Criteria: 4 Easy Ways

To use XLOOKUP with multiple criteria in Excel, you combine the criteria into a single lookup value using the ampersand (&) operator, then build a matching lookup array with the same operator. This article covers four reliable methods, including helper columns, boolean logic, and array formulas, with step-by-step examples and troubleshooting tips.

Why XLOOKUP Changed the Game

Before XLOOKUP, Excel users relied on VLOOKUP or INDEX/MATCH for lookups. VLOOKUP could only search the first column and required an exact match, making multi-criteria lookups clunky. XLOOKUP, introduced in 2020, searches any column, returns values from any column, and handles arrays natively. This makes multi-criteria lookups much simpler and more intuitive.

Method 1: Helper Column (Simplest)

The easiest way is to create a helper column that concatenates the criteria into one string, then use XLOOKUP on that column.

Insert a new column next to your data (e.g., column A).
In the first data row, enter a formula like =B2&"-"&C2 (assuming criteria are in columns B and C).
Drag the formula down to fill the column.
Use XLOOKUP with the concatenated lookup value: =XLOOKUP(F2&"-"&G2, A:A, D:D)

This method is straightforward and works with any version of Excel that supports XLOOKUP. The downside is the extra column, which can be hidden or placed far to the right.

Method 2: Boolean Logic (No Helper Column)

If you prefer not to add a helper column, you can use boolean multiplication inside XLOOKUP. This method treats each criterion as a condition and multiplies them together, which yields 1 only when all conditions are true.

The formula looks like this:

=XLOOKUP(1, (B:B=F2)*(C:C=G2), D:D)

Here’s how it works:

(B:B=F2) returns an array of TRUE/FALSE.
Multiplying two TRUE/FALSE arrays converts them to 1s and 0s, and the product is 1 only when both are TRUE.
XLOOKUP searches for the value 1 in that array and returns the corresponding value from column D.

This method is elegant but can be slower on very large datasets because it processes entire columns. To improve performance, limit the range (e.g., B2:B1000).

Method 3: Concatenation in the Lookup Array

Another no-helper approach is to concatenate the criteria directly in the lookup array. This is similar to the helper column but done on the fly.

=XLOOKUP(F2&"-"&G2, B:B&"-"&C:C, D:D)

The lookup array B:B&"-"&C:C creates a virtual array of concatenated values. This works well and avoids the helper column, but it also processes entire columns, so it’s best to use a defined range for efficiency.

Method 4: XLOOKUP with Multiple Columns (Array Formula)

If you need to return multiple values (e.g., both price and quantity), you can use XLOOKUP with an array return.

=XLOOKUP(F2&"-"&G2, B:B&"-"&C:C, D:E)

This returns two columns of results. To display them, you need to enter the formula as an array formula (Ctrl+Shift+Enter in older Excel, but in dynamic Excel it spills automatically). The result will spill into two cells horizontally.

How to Choose the Right Method

Helper column – best for beginners, small datasets, or when you need the concatenated value for other purposes.
Boolean logic – best when you want a single formula without extra columns, and your data isn’t huge.
Concatenation in array – similar to boolean but sometimes easier to read; also good for returning multiple columns.
Array return – use when you need multiple related values at once.

Common Problems and Solutions

Problem 1: #N/A error – This usually means no match was found. Check that your criteria exactly match the data (including spaces and text case). XLOOKUP is case-insensitive by default, but if you need case-sensitive matching, use the 5th argument: =XLOOKUP(...,"","",2) where 2 means binary (case-sensitive) match.

Problem 2: Wrong results due to spaces – Extra spaces in your data can break concatenation. Use TRIM on the criteria and data: =XLOOKUP(TRIM(F2)&"-"&TRIM(G2), TRIM(B:B)&"-"&TRIM(C:C), D:D) (array formula).

Problem 3: Slow performance – Full-column references cause Excel to process millions of cells. Limit the ranges to your actual data size (e.g., B2:B10000).

Problem 4: Duplicate matches – XLOOKUP returns the first match. If you need the last match, use the 6th argument: =XLOOKUP(...,"","","",-1) to search from the bottom.

Frequently Asked Questions

Can XLOOKUP handle more than two criteria?

Yes. Simply concatenate all criteria in both the lookup value and lookup array. For example, =XLOOKUP(F2&G2&H2, A:A&B:B&C:C, D:D).

Does XLOOKUP work with wildcards?

Yes, but only if you use the 5th argument set to 2 (wildcard match). For example, =XLOOKUP("*"&F2&"*", B:B, D:D, "", 2) will find partial matches.

Is XLOOKUP available in older Excel versions?

No. XLOOKUP is only available in Excel 2021, Excel 365, and Excel for the web. For older versions, use INDEX/MATCH with multiple criteria: =INDEX(D:D, MATCH(1, (B:B=F2)*(C:C=G2), 0)) entered as an array formula (Ctrl+Shift+Enter).

Why does my XLOOKUP return #VALUE! instead of #N/A?

#VALUE! usually means the lookup array and return array have different sizes. Ensure both ranges have the same number of rows. For example, B2:B100 and D2:D100.

Can I use cell references for the criteria instead of typing them?

Yes, that’s what we’ve done in all examples. Using cell references makes the formula dynamic – you can change the criteria and the formula updates automatically.

How do I return a value from a different sheet?

Simply reference the other sheet in the ranges. For example, =XLOOKUP(F2&G2, Sheet2!B:B&Sheet2!C:C, Sheet2!D:D).

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →