How to Use VLOOKUP With Multiple Criteria in Excel
Standard VLOOKUP only matches one column. Here's how to match on two or more criteria using INDEX-MATCH arrays.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Look up a value where both the name in column A and the department in column B match"
=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
- (A2:A100=F2) creates an array of TRUE/FALSE for name matches
- (B2:B100=G2) creates an array of TRUE/FALSE for department matches
- Multiplying these arrays gives 1 only where both conditions are TRUE
- MATCH(1, ..., 0) finds the first row where both criteria match
- 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
"Name="Alice", Dept="Sales""
Returns Alice's value from the Sales department row"Name="Bob", Dept="Engineering""
Returns Bob's value from the Engineering department rowFrequently 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 a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.