How to Use INDEX MATCH with Multiple Criteria?
Index Match with Multiple Criteria is a Excel function that retrieves a value from a specified range based on multiple criteria from different columns.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Combining multiple criteria in a lookup can be challenging, but using INDEX MATCH simplifies the process significantly.
The Formula
"Look up a value using two or more criteria columns simultaneously with INDEX MATCH, replacing a two-condition VLOOKUP"
=INDEX(return_range, MATCH(1, (criteria_range1=criteria1) * (criteria_range2=criteria2), 0))This formula retrieves a value from a specified range based on multiple criteria from different columns.
Step-by-Step Breakdown
- Define the return range where the desired value is located.
- Set the criteria ranges that contain the values to be matched.
- Specify the criteria that need to be met for each criteria range.
- Use the MATCH function to find the row number where both criteria are satisfied.
Edge Cases & Warnings
- The criteria ranges must be of the same size; otherwise, the formula will return an error.
- If no match is found, the formula will return an error (#N/A).
- Using non-unique values in criteria can lead to unexpected results.
Examples
"Lookup value in column C where A='Product1' and B='2023'"
Returns the corresponding value from column C."Find sales in column D where A='Product2' and B='2022'"
Returns the sales figure from column D.Frequently Asked Questions
Can I use more than two criteria?
Yes, you can extend the formula to include additional criteria by multiplying more conditions.
What if my criteria ranges are not the same size?
Ensure that all criteria ranges are of equal size to avoid errors.
How does this compare to VLOOKUP?
INDEX MATCH allows for more flexibility and can handle multiple criteria, unlike VLOOKUP.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.