VLOOKUP with Multiple Criteria in Excel

VLOOKUP can use multiple criteria if you combine the criteria into one lookup key. The safest method is to add a helper column to the source table, join the criteria there, and then run a normal exact-match VLOOKUP.

Example:

=VLOOKUP(H2&"|"&I2,$A$2:$E$100,5,FALSE)

This formula looks up a combined key, such as P-205|West, and returns the matching value from the fifth column of the lookup table.

1. When You Need Multiple Criteria

A normal VLOOKUP works when one value uniquely identifies the row. Multiple criteria are needed when one value is not enough.

If Product ID alone appears more than once, VLOOKUP returns the first match it finds. That may be the wrong row. Multiple criteria make the lookup key unique.

For broader wrong-result problems, see the VLOOKUP troubleshooting guide.

2. Example Data

Suppose your price table looks like this:

And your lookup area is:

The goal is to return 235, not the first P-205 row.

3. Best Method: Use a Helper Column

Add a helper column to the left of your source table. In A2, combine the criteria with a delimiter:

=B2&"|"&C2

Fill the helper formula down the source table.

Then use VLOOKUP against the helper key:

=VLOOKUP(H2&"|"&I2,$A$2:$E$100,5,FALSE)

Why this works:

This is the best method for shared workbooks because it is visible, easy to audit, and compatible with older Excel versions.

4. Why the Delimiter Matters

Do not simply join values without a separator:

=B2&C2

That can create accidental matches. For example:

Both rows become AB12, even though the criteria are different.

Use a delimiter that does not appear in your data:

=B2&"|"&C2

Good delimiters include |, ~, or another character your IDs and names never use.

5. Lock the Lookup Table Before Copying Down

When you copy the multiple-criteria VLOOKUP down a report, lock the source range:

=VLOOKUP(H2&"|"&I2,$A$2:$E$100,5,FALSE)

Without dollar signs, the lookup table can shift:

=VLOOKUP(H2&"|"&I2,A2:E100,5,FALSE)

That may work in the first row and fail later.

If absolute references are still fuzzy, review Excel cell references before filling formulas down a long list.

6. Multiple Criteria Across Sheets

The same helper-column method works when the source table is on another worksheet.

If your source table is on a sheet named Prices, use:

=VLOOKUP(A2&"|"&B2,Prices!$A$2:$E$100,5,FALSE)

If the sheet name has spaces, wrap it in single quotes:

=VLOOKUP(A2&"|"&B2,'Price List'!$A$2:$E$100,5,FALSE)

For more sheet-reference examples, see How to Use VLOOKUP Across Sheets in Excel.

7. Without a Helper Column: Use CHOOSE

You can build the combined lookup key inside the formula with CHOOSE:

=VLOOKUP(H2&"|"&I2,CHOOSE({1,2},$A$2:$A$100&"|"&$B$2:$B$100,$D$2:$D$100),2,FALSE)

This creates a temporary two-column lookup table:

Use this method when you cannot edit the source table. Otherwise, a helper column is usually easier to maintain.

Compatibility note: in older Excel versions, this may need to be confirmed as an array formula with Ctrl+Shift+Enter. In Microsoft 365 and newer Excel versions, it usually calculates normally.

8. XLOOKUP Alternative for Multiple Criteria

If you have Microsoft 365 or Excel 2021 or later, XLOOKUP is cleaner:

=XLOOKUP(1,($A$2:$A$100=H2)*($B$2:$B$100=I2),$D$2:$D$100,"Not found")

This formula tests both conditions and returns the row where both are true.

Use XLOOKUP for new modern workbooks. Use VLOOKUP with a helper column when compatibility matters or when your team is more comfortable auditing helper columns.

For a full modern lookup walkthrough, see Excel XLOOKUP tutorial.

9. INDEX MATCH Alternative

INDEX MATCH can also handle multiple criteria:

=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=H2)*($B$2:$B$100=I2),0))

This is useful when:

For a side-by-side decision guide, use VLOOKUP vs XLOOKUP vs INDEX MATCH.

10. Troubleshooting Multiple-Criteria VLOOKUP

Use this checklist when the formula returns #N/A or the wrong result:

To check whether the lookup key exists, use COUNTIF:

=COUNTIF($A$2:$A$100,H2&"|"&I2)

If the result is 0, VLOOKUP cannot find that combined key. If the result is greater than 1, your combined key is not unique.

If the key exists but VLOOKUP still returns #N/A, use How to Fix VLOOKUP #N/A in Excel to check spaces, data types, exact match settings, and table range issues.

For more counting examples, see the Excel COUNT function tutorial.

11. Practice Example

Use this source table:

Tasks:

Reference answers:

12. Summary

The best way to use VLOOKUP with multiple criteria is to create a helper column that combines the criteria into one unique key. Use a delimiter, lock the lookup range, and use exact match with FALSE. If you cannot add a helper column, use CHOOSE, XLOOKUP, or INDEX MATCH depending on your Excel version and workbook compatibility needs.

13. Related Guides

• Fix broader lookup problems with the VLOOKUP troubleshooting guide.
• Debug missing combined keys with How to Fix VLOOKUP #N/A in Excel.
• Build multiple-criteria lookups between tabs with VLOOKUP across sheets.
• Return every matching row instead of one helper-key result with Excel FILTER vs VLOOKUP.
• Compare alternatives in VLOOKUP vs XLOOKUP vs INDEX MATCH.
• Learn the modern lookup pattern in Excel XLOOKUP tutorial.
• Review Excel cell references before copying formulas down.