How to Use VLOOKUP with Multiple Criteria
This article explains three step-by-step methods for performing multiple-criteria lookups in practical work: a helper column, CHOOSE, and INDEX+MATCH.
Why Is VLOOKUP with Multiple Criteria Difficult?
VLOOKUP is designed to find a value based on one lookup key. To satisfy two or more criteria at the same time, such as “name + date,” you need to combine the criteria or use a different formula approach.
Sample Data
| Name | Date | Sales Amount |
|---|---|---|
| Kim Cheol-su | 2025-08-01 | 50,000 |
| Kim Cheol-su | 2025-08-02 | 70,000 |
| Lee Young-hee | 2025-08-01 | 60,000 |
Method 1) Helper Column
=A2 & "-" & TEXT(B2, "yyyy-mm-dd")Example: Kim Cheol-su-2025-08-01
=VLOOKUP("Kim Cheol-su-2025-08-02", $D$2:$E$4, 2, FALSE)- Pros: Simple and intuitive
- Cons: Requires modifying the original data
Method 2) CHOOSE Function
=VLOOKUP("Kim Cheol-su-2025-08-02",
CHOOSE({1,2}, A2:A4&"-"&TEXT(B2:B4,"yyyy-mm-dd"), C2:C4),
2, FALSE)- Pros: Keeps the original data unchanged
- Cons: The formula is complex
Method 3) INDEX + MATCH
=INDEX(C2:C4, MATCH(1, (A2:A4="Kim Cheol-su") * (B2:B4=DATE(2025,8,2)), 0))Array formula: Some versions require Ctrl + Shift + Enter
- Pros: No helper column required; highly flexible
- Cons: Requires an array formula
Method Comparison
| Method | Pros | Cons |
|---|---|---|
| Helper Column | Easy and fast | Requires modifying the original data |
| CHOOSE | Keeps original data unchanged | Complex formula |
| INDEX+MATCH | Highly flexible | Requires an array formula |
Practical Tips
- When comparing dates, standardize the format with TEXT() or DATE()
- INDEX+MATCH is recommended for large datasets
- You can extend the same approach to three or more criteria
Conclusion
VLOOKUP supports only one criterion by default, but you can perform multiple-criteria lookups by combining a helper column, CHOOSE, and INDEX+MATCH.