INDEX and MATCH with multiple criteria lets you look up a value based on two or more conditions, such as finding a sales figure for a specific product in a specific region. You enter the criteria in separate cells, and the formula returns the matching value from a result column. This is a powerful alternative to VLOOKUP when you need more flexibility.
Why Use INDEX and MATCH Instead of VLOOKUP?
VLOOKUP is the most well-known lookup function, but it has limitations. It can only search in the first column of a table and return a value to the right. It also struggles with multiple criteria without adding helper columns. INDEX and MATCH overcomes these issues. It can search any column and return a value from any column, to the left or right. When you need to match on more than one condition, you can combine MATCH with an array formula or use the newer functions like XLOOKUP in Excel 365. But INDEX and MATCH remains a reliable and widely compatible method.
How INDEX and MATCH Work Together
Before adding multiple criteria, it helps to understand the basic structure. The INDEX function returns a value from a specific row and column in a range. The MATCH function returns the position of a value in a row or column. When you combine them, MATCH finds the row number, and INDEX returns the value in that row from a specified column.
For a single criterion, the formula looks like this:
=INDEX(result_column, MATCH(lookup_value, lookup_column, 0))
For example, to find the price of a product named “Widget” in a table where column A has product names and column B has prices:
=INDEX(B:B, MATCH("Widget", A:A, 0))
This returns the price of the first “Widget” in the list.
Adding Multiple Criteria with Array Formulas
To match on multiple criteria, you need to create a unique identifier by combining the criteria. The classic approach uses an array formula. In Excel (pre-365) you press Ctrl+Shift+Enter, but in Excel 365 and Google Sheets, it works as a normal formula.
The formula structure is:
=INDEX(result_column, MATCH(1, (criteria1_range=criteria1) * (criteria2_range=criteria2), 0))
Here, each condition returns an array of TRUE/FALSE values. Multiplying them together converts TRUE to 1 and FALSE to 0. The product is 1 only when all conditions are TRUE. MATCH then finds the position of that 1.
Example: You have a table with columns: Product (A), Region (B), and Sales (C). You want the sales for “Widget” in “East”. The formula is:
=INDEX(C:C, MATCH(1, (A:A="Widget") * (B:B="East"), 0))
In older Excel, you must enter this as an array formula (Ctrl+Shift+Enter). In Excel 365 and Google Sheets, it works with a normal Enter.
Using the SUMPRODUCT Alternative
If you don’t want to deal with array formulas, you can use SUMPRODUCT to achieve the same result, but only when the result is a number. SUMPRODUCT multiplies arrays and sums the products. By setting the criteria to return 1 for matches and 0 for non-matches, you can extract the desired value.
The formula looks like:
=SUMPRODUCT((A:A="Widget") * (B:B="East") * C:C)
This works because each row with matching criteria contributes 1*1*C, and all others contribute 0. The sum is the sales figure. However, this only works if there is exactly one match and the result is numeric. If there are multiple matches, it sums them all, which may not be what you want.
Common Errors and How to Fix Them
When using INDEX and MATCH with multiple criteria, you may encounter errors. Here are the most common ones:
- #N/A error: This means no match was found. Check that the criteria exist in the table and that there are no extra spaces or different data types (text vs. number).
- #VALUE! error: This often occurs if you forget to press Ctrl+Shift+Enter in older Excel versions. The array formula is not being evaluated correctly.
- Wrong result: If you get a value but it’s not the one you expected, check for duplicate matches. The formula returns the first match. Ensure your criteria are unique or use additional conditions.
- Case sensitivity: INDEX and MATCH are not case-sensitive by default. If you need case-sensitive matching, you’ll need to use the EXACT function within the array formula.
Using INDEX and MATCH in Google Sheets
Google Sheets supports the same syntax as Excel. The array formula works without special entry, as Google Sheets automatically handles array formulas. You can also use the FILTER function as an alternative, which is often simpler for multiple criteria. For example:
=FILTER(C:C, A:A="Widget", B:B="East")
This returns all sales for Widget in East. If you only need the first one, you can wrap it with INDEX:
=INDEX(FILTER(C:C, A:A="Widget", B:B="East"), 1)
This is a cleaner solution in Google Sheets, but INDEX and MATCH still works.
Frequently Asked Questions
Can I use INDEX and MATCH with three or more criteria?
Yes, you can add more conditions by multiplying additional arrays. For example, to match on product, region, and year, use:
=INDEX(result, MATCH(1, (A:A="Widget") * (B:B="East") * (C:C=2024), 0))
Just make sure all ranges are the same size.
Why does my formula return #N/A even though the data exists?
This usually happens due to a mismatch in data types. For instance, if the lookup value is a number stored as text, or there are trailing spaces. Use the TRIM function to clean data, and ensure numbers are formatted consistently.
What is the difference between INDEX and MATCH and XLOOKUP?
XLOOKUP is a newer function in Excel 365 that can handle multiple criteria more directly. For example, you can use:
=XLOOKUP(1, (A:A="Widget") * (B:B="East"), C:C)
It’s simpler and doesn’t require array entry. However, XLOOKUP is not available in older Excel versions, so INDEX and MATCH is still useful for compatibility.
Can I use wildcards in INDEX and MATCH?
Yes, you can use wildcards in the MATCH function, but only when the lookup value is text and you use match_type 0. For example, to find the first product starting with “Wid”, use:
=MATCH("Wid*", A:A, 0)
But this only works for a single criterion, not for multiple criteria arrays.
Is there a way to avoid array formulas in older Excel?
Yes, you can use the SUMPRODUCT method for numeric results, or you can create a helper column that concatenates the criteria into a single unique ID. For example, add a column with =A2&B2, then use a simple MATCH on that column.