Excel intermediate vlookup index-match multiple criteria lookup

How to Use VLOOKUP With Multiple Criteria in Excel

VLOOKUP With Multiple Criteria is a Excel function that uses index-match with array multiplication to match multiple criteria simultaneously. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Standard VLOOKUP only matches one column. Here's how to match on two or more criteria using INDEX-MATCH arrays.

Generate This Formula Browse Library

The Formula

Prompt

"Look up a value where both the name in column A and the department in column B match"

Excel 
=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

This formula uses INDEX-MATCH with array multiplication to match multiple criteria simultaneously. Each condition creates a TRUE/FALSE array, and multiplying them together produces 1 only where ALL conditions are true.

Step-by-Step Breakdown

  1. (A2:A100=F2) creates an array of TRUE/FALSE for name matches
  2. (B2:B100=G2) creates an array of TRUE/FALSE for department matches
  3. Multiplying these arrays gives 1 only where both conditions are TRUE
  4. MATCH(1, ..., 0) finds the first row where both criteria match
  5. INDEX returns the value from column C at that row position

Edge Cases & Warnings

  • Returns #N/A if no match is found — wrap with IFERROR to handle gracefully
  • Must be entered as an array formula with Ctrl+Shift+Enter in Excel 2019 and earlier
  • In Excel 365, dynamic arrays handle this natively without Ctrl+Shift+Enter
  • Case-insensitive by default — use EXACT() for case-sensitive matching

Examples

Prompt

"Name="Alice", Dept="Sales""

Excel 
Returns Alice's value from the Sales department row
Prompt

"Name="Bob", Dept="Engineering""

Excel 
Returns Bob's value from the Engineering department row

Frequently Asked Questions

Can I use VLOOKUP with two criteria directly?

No. Standard VLOOKUP only accepts one lookup value. Use INDEX-MATCH with array multiplication, or create a helper column that concatenates your criteria.

Is INDEX-MATCH faster than VLOOKUP?

Yes. INDEX-MATCH doesn't require scanning from left to right, and it can look up values in any column direction. It's also more flexible for multiple criteria.

Do I need Ctrl+Shift+Enter?

In Excel 2019 and earlier, yes — this is an array formula. In Excel 365 and 2021, dynamic arrays handle it automatically.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples