Complete Guide to Excel INDEX MATCH with Multiple Criteria
Learn how to efficiently use INDEX and MATCH functions for multi-criteria lookups in Excel, enhancing your data analysis skills.
What is Excel INDEX MATCH with Multiple Criteria?
Basic Definition
Excel's INDEX and MATCH functions are powerful tools for performing multi-criteria lookups, allowing users to efficiently retrieve data based on several conditions.
- Combines multiple criteria for precise data retrieval
- INDEX returns a value from a specified range
- MATCH finds the position of a value in a dataset
- Supports array formulas for advanced lookups
Key Advantages
Allows for complex queries using multiple conditions
Can be used with SUMPRODUCT to simplify calculations
Provides flexibility and customization in data analysis
Function Syntax
=INDEX(E5:E11, MATCH(1, (A:A="条件1")*(B:B="条件2"), 0))
lookup_value
The value to search for
lookup_array
The range to search within
return_array
The range containing the return values
if_not_found (Optional)
Value to return if no match is found
match_mode (Optional)
0: Exact match; -1: Exact or next smaller; 1: Exact or next larger
search_mode (Optional)
1: First-to-last; -1: Last-to-first; 2: Binary search
Usage Examples
Basic Example
Looking up contact phone number for a specified customer ID in customer data table
=INDEX(E5:E11, MATCH(1, (A:A="条件1")*(B:B="条件2"), 0))
Advanced Example
Cross-sheet lookup with multiple matching results
=INDEX(Sheet2!C:E, MATCH(A2, Sheet2!B:B, 0), 1)
Use Our Product, No Complex Functions to Memorize
Easily complete data lookup and analysis through a simple interface
Try NowTroubleshooting
#N/A Error
Common error when no match is found, can be handled by setting the if_not_found parameter
Data Type Mismatch
Ensure lookup value and range data types match
Performance Optimization
Use binary search mode to improve efficiency with large datasets
Function Comparison
| Feature | XLOOKUP | VLOOKUP | INDEX+MATCH |
|---|---|---|---|
| Lookup Direction | Bi-directional | Right only | Bi-directional |
| Multiple Column Return | Supported | Not supported | Supported |
| Error Handling | Built-in | Additional handling needed | Additional handling needed |
Start Using Our Product
Process data quickly through an intuitive interface without writing complex functions