To use XLOOKUP with multiple criteria in Excel, you combine the criteria into a single lookup value using the ampersand (&) operator, and do the same for the lookup array. For example, =XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100) returns the value in column C where column A matches F2 and column B matches G2.
Why XLOOKUP Handles Multiple Criteria Differently
Older lookup functions like VLOOKUP and INDEX/MATCH require helper columns or array formulas to check more than one condition. XLOOKUP, introduced in Excel 2021 and Microsoft 365, accepts arrays directly for both the lookup value and the lookup array. When you concatenate two ranges with &, Excel creates an array of combined text strings, and XLOOKUP searches for the concatenated lookup value within that array. This works because XLOOKUP is designed to handle array operations natively, without needing Ctrl+Shift+Enter (though in older Excel versions you may need to confirm with CSE).
Step-by-Step: Build a Multiple Criteria XLOOKUP
Assume your data has product names in column A, colors in column B, and prices in column C. You want the price for a specific product and color entered in cells F2 and G2.
- In a cell, type
=XLOOKUP(. - For the lookup value, reference the two criteria cells joined with &:
F2&G2. - For the lookup array, join the two criteria columns with &:
A2:A100&B2:B100. - For the return array, select the column to return:
C2:C100. - Optionally add the not_found argument:
"Not found". - Close the parenthesis and press Enter. The full formula:
=XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100, "Not found")
If the product “T-Shirt” in F2 and color “Red” in G2 exist in the data, the formula returns the corresponding price. If not, it returns “Not found”.
Using Boolean Logic for Multiple Criteria
Another approach uses Boolean multiplication to create a 1/0 array. The formula =XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100) works because the multiplication of two TRUE/FALSE arrays yields 1 only when both conditions are true. This method is useful when you want to avoid concatenation issues with numbers or dates, and it also allows you to use comparison operators like greater than or less than. For example, =XLOOKUP(1, (A2:A100=F2)*(C2:C100>100), D2:D100) finds the first row where column A matches F2 and column C is greater than 100.
How to Choose Between Concatenation and Boolean
Both methods return the same result for exact matches. Concatenation is simpler to type and read, but it can fail if your criteria contain the delimiter character. For instance, if a product name is “A-B” and a color is “B”, concatenating with & gives “A-BB”, which might accidentally match another combination. Boolean logic avoids this because it compares each cell separately. Use Boolean logic when your criteria are numeric, dates, or contain special characters. Also, Boolean logic works with approximate match if you set the match_mode argument, though it is rarely needed.
Common Problems and Fixes
If your multiple criteria XLOOKUP returns #N/A, check these issues: the criteria cells are not exactly matching due to extra spaces (use TRIM), the data types differ (numbers stored as text), or the ranges are not the same size. Another frequent problem is that XLOOKUP returns the first match only; if you need all matches, use the FILTER function instead. Also, remember that XLOOKUP is case-insensitive by default. If you need case-sensitive matching, use the match_mode argument set to 0 and combine with EXACT, but that requires an array formula. For example, =XLOOKUP(1, EXACT(A2:A100,F2)*EXACT(B2:B100,G2), C2:C100).
Comparing XLOOKUP with INDEX/MATCH and FILTER
INDEX/MATCH can also handle multiple criteria with array formulas: =INDEX(C2:C100, MATCH(1, (A2:A100=F2)*(B2:B100=G2), 0)). This requires Ctrl+Shift+Enter in older Excel, but in Excel 365 it works normally. XLOOKUP is simpler and has a built-in not_found argument. However, INDEX/MATCH is more flexible for returning values to the left or right of the lookup array, though XLOOKUP also supports that with its return_array. For returning multiple matches, FILTER is the right tool: =FILTER(C2:C100, (A2:A100=F2)*(B2:B100=G2)) returns all matching prices. Use FILTER when you need a list, not a single value.
Version Requirements and Mac Differences
XLOOKUP requires Excel 2021 or Microsoft 365. It is not available in Excel 2019 or earlier. On Mac, the function works identically, but you may need to use Command+Return instead of Ctrl+Shift+Enter for array formulas in older versions. In Excel for the web, XLOOKUP is fully supported. If you are using an older Excel, you must use INDEX/MATCH with array entry.
Frequently Asked Questions
Can XLOOKUP handle three or more criteria? Yes, simply concatenate all criteria and all lookup columns: =XLOOKUP(F2&G2&H2, A2:A100&B2:B100&C2:C100, D2:D100) for three criteria. The same applies to Boolean logic: multiply all condition arrays.
Why does my XLOOKUP return #VALUE! instead of a result? This usually happens when the lookup array and return array are not the same size, or when you use array operations without proper syntax. Check that the ranges have the same number of rows. Also, if you concatenate ranges, ensure they are all one-dimensional columns.
Does XLOOKUP work with wildcards for multiple criteria? Yes, you can use wildcards in the lookup value, but only if you set the match_mode argument to 2. For example, =XLOOKUP("*"&F2&"*", A2:A100&B2:B100, C2:C100, , 2) finds a partial match. Note that wildcards work on the concatenated string, so be careful.
Is there a way to do multiple criteria with XLOOKUP without concatenation? Yes, the Boolean method =XLOOKUP(1, (range1=crit1)*(range2=crit2), return) avoids concatenation. This is often safer for numeric or date criteria.
Can I use XLOOKUP to return multiple columns with multiple criteria? Yes, set the return_array to multiple columns, for example C2:D100. The formula will return the entire row for the first match. To get multiple rows, you need FILTER.
Related
- Excel Want to Merge? How to Combine Cells
- Excel Vertical Indent: How to Indent Down a Cell
- Excel Strikethrough: How to Apply & Remove It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text…
- Excel Filter for Duplicate Values: Find and Review ThemTo filter duplicate values in Excel for Microsoft 365 on Windows, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill…
- Excel Filter: Select Multiple Values with a ShortcutIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off; this shortcut does…
- Excel Highlight Unique Duplicates: 2 WaysTo highlight unique duplicates in Excel, use Conditional Formatting with a formula that counts occurrences: select your data…
- Excel WEEKDAY vs WORKDAY: What Each Function Does in ExcelExcel’s WEEKDAY function tells you which day of the week a date falls on, as a number; WORKDAY…
- Excel Split Cell by Line Break: Formulas and Steps ExplainedTo split a cell at each line break in Excel for Microsoft 365 on Windows, enter =TEXTSPLIT(A2,CHAR(10)) in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.