XLOOKUP With Multiple Criteria in Excel

XLOOKUP only takes one lookup value, but you can match on two or more columns. Here are two working formulas, plus what to use if you do not have Excel 365.

Quick answer

Multiply the conditions inside the lookup array:

=XLOOKUP(1,(A2:A100=F1)*(B2:B100=F2),C2:C100,"Not found")

This returns the value from column C where column A equals F1 and column B equals F2.

Method 1: Multiply the conditions

Each condition returns TRUE or FALSE. Multiplying them gives 1 only where all conditions are TRUE, so XLOOKUP searches for 1.

  • Add a third condition: (A2:A100=F1)*(B2:B100=F2)*(D2:D100=F3)
  • OR logic: use + instead of * and test for greater than zero, for example =XLOOKUP(TRUE,((A2:A100=F1)+(B2:B100=F2))>0,C2:C100,"Not found").

Method 2: Join the columns with &

=XLOOKUP(F1&F2,A2:A100&B2:B100,C2:C100,"Not found")

This joins the two lookup values and the two columns into one text key. It is short and easy to read. If values could run together (for example “1”+”23″ and “12”+”3″), put a separator in: F1&"|"&F2 and A2:A100&"|"&B2:B100.

Example

A sales table has Region in A, Product in B and Sales in C. In F1 type “East” and in F2 type “Widget”. The formula returns the sales for East and Widget only.

Return several matches

XLOOKUP returns only the first match. To return all rows that match, use FILTER: =FILTER(C2:C100,(A2:A100=F1)*(B2:B100=F2)).

No XLOOKUP? Use INDEX MATCH

XLOOKUP needs Excel 2021 or Microsoft 365. In older versions use an array formula: =INDEX(C2:C100,MATCH(1,(A2:A100=F1)*(B2:B100=F2),0)), confirmed with Ctrl + Shift + Enter. See INDEX MATCH in Excel.

Common errors

  • #N/A: no row matches all conditions. Check spelling and extra spaces.
  • #VALUE!: the arrays have different sizes. All ranges must have the same number of rows.
  • Wrong result: numbers stored as text. Convert them to numbers.

Frequently asked questions

Can XLOOKUP use multiple criteria?

Yes. Multiply the conditions inside the lookup array, such as =XLOOKUP(1,(A2:A100=F1)*(B2:B100=F2),C2:C100), or join the columns with an ampersand.

Does XLOOKUP with multiple criteria work in Excel 2019?

No. XLOOKUP is available in Excel 2021 and Microsoft 365. In Excel 2019 use INDEX and MATCH as an array formula.

How do I return all matches, not just the first?

Use the FILTER function with the same multiplied conditions.

Why does my XLOOKUP return #N/A?

No row meets all the conditions at once. Check spelling, extra spaces and numbers stored as text.

XLOOKUP in Excel · INDEX MATCH in Excel · How to use VLOOKUP · IF formula in Excel · All free calculators