VLOOKUP With Multiple Criteria in Excel

To look up a value that matches two or more conditions, such as the sales for North *and* Pen, join the conditions together. Here are three ways: XLOOKUP for Excel 365, INDEX and MATCH for every version, and a helper column for plain VLOOKUP.

Excel 365: XLOOKUP with joined criteria

North appears three times and Pen twice, but only one row has both. Join the two criteria, and join the two columns, so XLOOKUP looks for “NorthPen”:

The only row with North and Pen is row 6, so the result is 300. A plain lookup on North alone would have returned 450, the first North row.

How the formula works

1. G2&G3 joins the criteria into NorthPen.
2. B3:B10&C3:C10 joins the two columns row by row: NorthPencil, SouthPen, EastNotebook, NorthPen…
3. XLOOKUP finds NorthPen in that joined list and returns the matching value from D3:D10.

Any version: INDEX and MATCH

Each test returns TRUE or FALSE for every row. Multiplying them gives 1 only where both are TRUE, and MATCH finds that 1:

The result is the same, 300. In Excel 2019 and earlier, confirm the formula with Ctrl+Shift+Enter.

Plain VLOOKUP: add a helper column

VLOOKUP searches only one column, so build one: column B joins region and product with a dash. Then look up the joined value:

VLOOKUP finds North-Pen in the helper column and returns column 4, Sales: 300. The dash stops mix-ups such as “AB”+”C” matching “A”+”BC”.

Which method to use

For three or more criteria, keep joining: G2&G3&G4 and B3:B10&C3:C10&D3:D10.

Notes

• If no row matches every criterion, the formula returns #N/A. Use XLOOKUP’s fourth argument or IFERROR for a message.
• If several rows match, these formulas return the first one. To return all of them, use FILTER.
• Joined criteria are text, so numbers stored as text and as numbers still match.

Common errors

Related