Excel XLOOKUP on Two Criteria: How-To

Formulas & functions · Updated November 22, 2026

To look up a value using two criteria in Excel, use the XLOOKUP function with a concatenated lookup value and a concatenated lookup array: =XLOOKUP(A2&B2, C:C&D:D, E:E). This joins the two criteria into a single search key and searches a joined range, returning the matching value from the result column.

Why XLOOKUP Beats Older Lookup Functions

Before XLOOKUP (introduced in Excel 2021 and Microsoft 365), multi-criteria lookups required array formulas with INDEX and MATCH, or the SUMPRODUCT hack. Those approaches worked but were harder to read and maintain. XLOOKUP simplifies the logic: you give it a lookup value, a lookup array, and a return array. When you need two criteria, you simply build a composite key from both columns. This works because XLOOKUP handles arrays natively without needing Ctrl+Shift+Enter (though older Excel versions may require it for array operations).

How the Concatenation Method Works

The core trick is joining the two criteria into one string. For example, if you have a table with Product in column A, Region in column B, and Sales in column C, and you want to find sales for “Widget” in “East”:

  • In a cell, enter the product and region you’re looking for (say, D2 and E2).
  • Use the formula: =XLOOKUP(D2&E2, A:A&B:B, C:C)
  • The & operator concatenates the values, so “Widget”&”East” becomes “WidgetEast”.
  • The lookup array A:A&B:B creates a similar concatenated array for every row.

This returns the sales value from column C. If there are duplicate combinations, XLOOKUP returns the first match by default. To get the last match, add the optional match_mode argument: =XLOOKUP(D2&E2, A:A&B:B, C:C, , -1).

Handling Numeric and Date Criteria

When your criteria are numbers or dates, concatenation can cause problems. For example, if you have product ID 1 and region ID 2, the concatenated key becomes “12”, which could also match product ID 12 with region ID (blank) or other combinations. To avoid ambiguity, add a delimiter that won’t appear in your data, like a hyphen or pipe: =XLOOKUP(D2&"-"&E2, A:A&"-"&B:B, C:C). This turns “1” and “2” into “1-2”, which is unique. For dates, ensure both the lookup value and the lookup array use the same format—either as text or as serial numbers. If your dates are true Excel dates, concatenating them produces numbers like 45000, so make sure the lookup value is also a date, not text.

When to Use XLOOKUP vs. INDEX/MATCH or FILTER

XLOOKUP with concatenation is the simplest for two criteria, but it has limitations. If you need to return multiple matches, use the FILTER function instead: =FILTER(C:C, (A:A=D2)*(B:B=E2)). This returns all matching sales values. For older Excel versions (2019 and earlier), you must use INDEX and MATCH with array entry: {=INDEX(C:C, MATCH(1, (A:A=D2)*(B:B=E2), 0))}—remember to press Ctrl+Shift+Enter. XLOOKUP is also faster on large datasets because it uses binary search by default, whereas INDEX/MATCH with concatenation may be slower due to array calculations.

Common Pitfalls and How to Fix Them

  • #N/A errors: This means no exact match was found. Check for extra spaces in your criteria or data (use TRIM), or ensure the concatenation delimiter matches.
  • Mixed data types: If one column contains numbers stored as text, concatenation may fail. Convert all to the same type using TEXT or VALUE.
  • Whole-column references: Using A:A is fine, but it can slow down calculations. Use a defined range like A2:A1000 for better performance.
  • Duplicate keys: If your two criteria aren’t unique together, XLOOKUP returns the first occurrence. Use FILTER or add a third criterion to make it unique.
  • Case sensitivity: XLOOKUP is case-insensitive by default. To make it case-sensitive, use the match_mode argument with a value of 0 and combine with EXACT in an array formula, but that’s rarely needed.

Step-by-Step Example with Real Data

Suppose you have an employee table: Last Name in A2:A20, First Name in B2:B20, and Salary in C2:C20. You want to find the salary for “Smith, John”.

  1. In E2, enter the last name “Smith”. In F2, enter the first name “John”.
  2. In G2, enter the formula: =XLOOKUP(E2&F2, A2:A20&B2:B20, C2:C20)
  3. Press Enter. The result is the salary for that person.

If you get #N/A, check that there are no extra spaces. You can use TRIM in the formula: =XLOOKUP(TRIM(E2)&TRIM(F2), TRIM(A2:A20)&TRIM(B2:B20), C2:C20).

Alternatives: Using Helper Columns and Boolean Logic

If you prefer not to use concatenation in the formula, you can create a helper column that joins the two criteria. For example, in column D, enter =A2&"-"&B2 and drag down. Then use a simple XLOOKUP: =XLOOKUP(E2&"-"&F2, D2:D20, C2:C20). This is easier to debug because you can see the combined keys. Another alternative is to use Boolean multiplication with INDEX and MATCH for older Excel versions, but it requires array entry. For dynamic arrays, FILTER is the most flexible for multiple criteria and multiple results.

Frequently Asked Questions

Can XLOOKUP handle three criteria? Yes, just concatenate all three: =XLOOKUP(A2&B2&C2, D:D&E:E&F:F, G:G). Use a delimiter to avoid ambiguity.

Why does my XLOOKUP return #VALUE! instead of #N/A? This usually happens when the lookup array contains errors or when concatenation produces a mismatch in data types. Check for hidden characters or use TRIM and CLEAN.

Is XLOOKUP available in Excel 2019? No, XLOOKUP requires Excel 2021 or Microsoft 365. For Excel 2019, use INDEX and MATCH with array entry.

How do I make XLOOKUP case-sensitive with two criteria? XLOOKUP doesn’t have a case-sensitive option. Use INDEX with MATCH and EXACT in an array formula, or use FILTER with EXACT in Microsoft 365.

Can I use XLOOKUP with wildcards for partial matches? Yes, set the match_mode to 2 (wildcard match). For example, =XLOOKUP("*"&E2&"*"&F2, A:A&B:B, C:C, , 2) finds partial matches.

Performance and Version Considerations

XLOOKUP is optimized for speed, but concatenating entire columns creates an array in memory. On very large datasets (100,000+ rows), this can slow down. To improve performance, limit the range to the actual data (e.g., A2:A100000) and avoid whole-column references. Also, if you’re using Excel for the web or Mac, the syntax is identical, but older Mac versions may require pressing Cmd+Return for array formulas if you use INDEX/MATCH. Always ensure your data is clean—remove duplicates if you expect unique matches—and test the formula on a small sample first.

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.