Excel XLOOKUP Multiple Criteria: Return a Value

Formulas & functions · Updated November 22, 2026

To return a value in Excel using XLOOKUP with multiple criteria, combine the criteria into a single lookup value with the ampersand (&) operator, and combine the corresponding lookup arrays the same way. For example, =XLOOKUP(F2&G2, A:A&B:B, C:C) returns the value in column C where column A matches F2 and column B matches G2.

Why XLOOKUP for Multiple Criteria?

Before XLOOKUP, Excel users relied on INDEX/MATCH arrays or SUMPRODUCT for multi-criteria lookups. Those work, but they are clunky and often require Ctrl+Shift+Enter (array formulas) in older versions. XLOOKUP, introduced in Excel 2021 and Microsoft 365, handles multiple criteria with a simple concatenation trick. It is faster to write, easier to read, and recalculates dynamically. Unlike VLOOKUP, XLOOKUP can look to the left, return an entire row or column, and has built-in error handling. For these reasons, XLOOKUP has become the go-to function for modern Excel users.

How the Concatenation Method Works

The core idea is to create a unique identifier by joining the values from each criteria column. For instance, if you have a product list with columns: A (Product ID), B (Color), and C (Price), and you want the price for Product ID “P100” in “Red”, you would write:

=XLOOKUP("P100"&"Red", A:A&B:B, C:C)

But hardcoding values is rare. Usually you reference cells. With lookup values in F2 and G2, the formula becomes:

=XLOOKUP(F2&G2, A:A&B:B, C:C)

This works because A:A&B:B creates an array of concatenated values like “P100Red”, “P101Blue”, etc. The lookup value F2&G2 creates “P100Red”, and XLOOKUP finds its position. The function then returns the corresponding value from C:C.

Step-by-Step Example with Real Data

Assume your sheet has:

  • A2:A20 = Product IDs (e.g., “A-101”)
  • B2:B20 = Color (e.g., “Red”)
  • C2:C20 = Price (e.g., 25.50)
  • F2 = the product ID you want to look up (e.g., “A-105”)
  • G2 = the color you want (e.g., “Blue”)

Place this formula in H2:

=XLOOKUP(F2&G2, A2:A20&B2:B20, C2:C20)

If row 5 has A-105 in A5, Blue in B5, and 19.99 in C5, the formula returns 19.99. If no match exists, XLOOKUP returns #N/A by default. You can add a fourth argument to customize that: =XLOOKUP(F2&G2, A2:A20&B2:B20, C2:C20, "Not found").

Choosing Between XLOOKUP and Other Methods

XLOOKUP is the best choice for most users because it is simple and fast. However, there are situations where alternatives work better:

  • INDEX/MATCH with arrays: Use when you need to look up based on multiple criteria without concatenation, especially if your criteria columns contain numbers that might concatenate ambiguously (e.g., 1 and 23 vs. 12 and 3). The formula =INDEX(C2:C20, MATCH(1, (A2:A20=F2)*(B2:B20=G2), 0)) requires Ctrl+Shift+Enter in older Excel, but in Excel 365 it works normally.
  • FILTER function: If you need to return multiple matching values, not just the first, use =FILTER(C2:C20, (A2:A20=F2)*(B2:B20=G2)). This returns an array of all prices that match both criteria.
  • SUMPRODUCT: Only if you need a numeric result and you are sure there is only one match. It can return a value but is less flexible.

For most single-result lookups, XLOOKUP with concatenation is the easiest to write and maintain.

Common Problems and How to Fix Them

  • #N/A error: No match found. Check that your criteria cells have no extra spaces. Use TRIM: =XLOOKUP(TRIM(F2)&TRIM(G2), TRIM(A2:A20)&TRIM(B2:B20), C2:C20).
  • Wrong results due to ambiguous concatenation: If your criteria are numeric and can combine ambiguously (e.g., 1 and 23 vs. 12 and 3), add a delimiter: =XLOOKUP(F2&"|"&G2, A2:A20&"|"&B2:B20, C2:C20). Use a character unlikely to appear in your data.
  • Data types mismatch: If lookup values are numbers but criteria cells are text, or vice versa, convert with TEXT: =XLOOKUP(TEXT(F2,"0")&G2, TEXT(A2:A20,"0")&B2:B20, C2:C20).
  • Spill errors with arrays: If you use entire columns (A:A) and your Excel version doesn’t support dynamic arrays, you may get a #SPILL! error. Use a specific range like A2:A1000 instead.

XLOOKUP vs. INDEX/MATCH for Multiple Criteria

INDEX/MATCH has been the traditional workaround. The classic formula is:

=INDEX(C2:C20, MATCH(1, (A2:A20=F2)*(B2:B20=G2), 0))

In Excel 365, this works without Ctrl+Shift+Enter, but in older versions you must press Ctrl+Shift+Enter. XLOOKUP is shorter and doesn’t require array entry. However, INDEX/MATCH is more flexible if you need to perform a lookup where the criteria are not adjacent or if you need to handle numeric criteria without concatenation. For most users, XLOOKUP is easier to learn and debug.

Frequently Asked Questions

Can XLOOKUP handle more than two criteria?

Yes, simply concatenate all criteria. For three criteria in F2, G2, H2, use =XLOOKUP(F2&G2&H2, A:A&B:B&C:C, D:D). The same delimiter trick applies if needed.

What if my criteria are in different sheets?

Reference the other sheet in the lookup arrays. For example, =XLOOKUP(F2&G2, Sheet2!A:A&Sheet2!B:B, Sheet2!C:C). Ensure the sheet name is spelled correctly and use single quotes if it contains spaces.

Does XLOOKUP work in Excel 2019 or older?

No. XLOOKUP was introduced in Excel 2021 and Microsoft 365. For older versions, use INDEX/MATCH with Ctrl+Shift+Enter or SUMPRODUCT.

How do I return a value from a column to the left of my criteria?

XLOOKUP handles this naturally because you specify the return array separately. For example, if your criteria are in B and C, but you want a value from A, use =XLOOKUP(F2&G2, B:B&C:C, A:A).

Can I use wildcards with XLOOKUP for partial matches?

Yes, set the match mode argument to 2 (wildcard). For example, =XLOOKUP("*"&F2&"*", A:A, B:B, , 2). But when using concatenation for multiple criteria, wildcards apply to the entire concatenated string, which can be tricky. Test carefully.

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

This often happens when the concatenated arrays have different sizes or when there are errors in the data. Ensure all ranges have the same number of rows. Also check for any cells with errors in the lookup columns.

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.