Looking up data in Excel is one of the most fundamental and important tasks in workflow automation and data analysis. Among its functions, VLOOKUP is widely used to find related data based on a specific key value. However, because standard VLOOKUP supports only a single criterion, it can become challenging when you need to use two or more criteria.
This article covers a range of ways to look up data in Excel based on multiple criteria. From methods using a helper column to the MAXIFS, MINIFS, and FILTER functions available only in newer Excel versions, each approach is explained with examples, along with its advantages, disadvantages, and practical application tips.
📌 Why Do You Need Multiple-Criteria Lookups?
Consider the following situations:
- When one salesperson (A) sells multiple products and you want to look up sales for a specific product sold by that salesperson
- When extracting data based on a date + category combination
- When you need to find individual performance data using an employee name + department combination
In these situations, it is difficult to find the exact value you need with VLOOKUP using only a single criterion. The key is to appropriately combine criteria, use array formulas, or use newer functions.
✅ Method 1: Helper Column
This is a basic method, but it offers the best compatibility and accessibility. Create a lookup key by combining two or more criteria as text, then use VLOOKUP based on that key.
Example Table
| Salesperson | Product | Sales | Lookup Key |
|---|---|---|---|
| Anna | Bag | 120 | Anna_Bag |
| Ben | Shoes | 220 | Ben_Shoes |
| Anna | Watch | 180 | Anna_Watch |
=VLOOKUP(“Anna_Bag”, D2:E10, 2, FALSE)
Advantages: Simple and works in every version of Excel
Disadvantages: Requires an additional helper column, and errors can occur if the delimiter appears in the data
✅ Method 2: Multiple Criteria with INDEX + MATCH
This approach is more flexible than VLOOKUP and handles multiple criteria and moving ranges well.
=INDEX(C2:C10, MATCH(1, (A2:A10=”Anna”)*(B2:B10=”Bag”), 0))
Because this method uses an array formula, you must press Ctrl+Shift+Enter after entering the formula to return a result (in older versions). In Excel 365 and later, simply press Enter.
✅ Method 3: Using MAXIFS / MINIFS
The MAXIFS function returns the largest value among rows that meet the criteria. Conversely, MINIFS finds the smallest value.
=MAXIFS(C2:C10, A2:A10, “Anna”, B2:B10, “Watch”)
Advantages: Easy to read and easy to add multiple criteria
Disadvantages: Available only to Excel 2019 and later or Microsoft 365 users
✅ Method 4: Return All Multiple-Criteria Results with FILTER
This is one of the best methods for Excel 365 users. It returns all data that meets the criteria as an array.
=FILTER(C2:C10, (A2:A10=”Anna”)*(B2:B10=”Bag”))
It is especially useful for data analysis, dashboard creation, and dynamic reports.
📊 Comparison of Each Method
| Method | Version Compatibility | Ease of Learning | Advantages | Disadvantages |
|---|---|---|---|---|
| Helper Column + VLOOKUP | All versions | ★★★★★ | Simple and intuitive | Requires an additional column |
| INDEX + MATCH | All versions | ★★★☆☆ | Highly flexible | Requires an array formula |
| MAXIFS | 2019 and later | ★★★★☆ | Returns the maximum value by criteria | Version limitations |
| FILTER | 365 and later | ★★★★☆ | Returns all results as an array | Requires a newer version |
🛠 Practical Application Tips
- The VLOOKUP helper column method is useful for organizing ERP data and performing simple analysis.
- INDEX+MATCH is suitable when combining multiple columns or when criteria ranges change frequently.
- MAXIFS/MINIFS work well for KPIs and performance management.
- FILTER is the most recommended option for real-time data filtering and report automation.
🔍 SEO Optimization Strategy
- Primary keyword: Excel VLOOKUP multiple criteria
- Related keywords: FILTER function, MAXIFS examples, INDEX MATCH multiple criteria
- Place keywords in the title, subheadings, image alt text, and meta description.
- Provide comprehensive information with at least 5,000 characters.
📚 Conclusion
Multiple-criteria lookups in Excel are an essential skill that can greatly improve data quality and analysis accuracy. Choose the approach that best fits your situation from the four methods introduced in this article and apply it to your work.
You can easily solve the criteria-based lookup problems commonly encountered in practice and take your automation to the next level.
If you have questions or feedback, please leave a comment. We will continue to introduce a variety of ways to use Excel.