VLOOKUP with Multiple Conditions in Excel

Why VLOOKUP struggles with multiple conditions

VLOOKUP searches one column for one value. When you need to match on two conditions (e.g. find the price for a specific product in a specific region), you need a workaround.

Method 1 — Helper column (simplest)

Combine your lookup conditions into one concatenated column:

-- In the lookup table, add a helper column:
=A2&B2       or     =A2&"-"&B2
-- Combines "North" and "Widget A" into "NorthWidget A"

-- Then VLOOKUP on the helper column:
=VLOOKUP(D2&E2, helper_column:return_column, 2, 0)
-- Where D2=region, E2=product

Method 2 — CHOOSE array (no helper column)

=VLOOKUP(D2&E2, CHOOSE({1,2}, A2:A100&B2:B100, C2:C100), 2, 0)
-- CHOOSE creates a virtual two-column array
-- First column: concatenated lookup values
-- Second column: the return values
-- Enter with Ctrl+Shift+Enter in older Excel

Method 3 — INDEX MATCH with multiple criteria (recommended)

=INDEX(C2:C100, MATCH(1, (A2:A100=D2)*(B2:B100=E2), 0))
-- Enter with Ctrl+Shift+Enter (older Excel)
-- Or just Enter in Excel 365 (dynamic arrays handle it)

-- A2:A100=D2 returns TRUE/FALSE array
-- B2:B100=E2 returns TRUE/FALSE array
-- Multiplying gives 1 only where BOTH match
-- MATCH finds the position of the first 1

Method 4 — XLOOKUP (cleanest, Excel 365)

=XLOOKUP(D2&E2, A2:A100&B2:B100, C2:C100, "Not found")
-- Concatenate lookup values and lookup arrays inline
-- No helper column, no array entry needed
-- Cleanest syntax of all methods

✅ Which method to use

Excel 365: use XLOOKUP concatenation — cleanest and simplest. Excel 2019 and earlier: use INDEX MATCH with array entry (Ctrl+Shift+Enter). Helper column works in all versions but modifies your data structure.

Now practise it for real

Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.

Start the Excel Basics track free →

VLOOKUP with multiple conditionsin Excel