Excel INDEX MATCH Multiple Criteria: Formula Examples
This article explains how to use Excel’s INDEX and MATCH functions together to look up a value based on two or more criteria, with concrete formula examples you can copy. You’ll learn the exact syntax for array and non-array versions, how to handle text and numeric criteria, and how to troubleshoot common errors.
Why INDEX and MATCH Instead of VLOOKUP?
VLOOKUP is the default lookup tool for many Excel users, but it has significant limitations: it can only look to the right of the lookup column, it requires the lookup column to be the first column in the table, and it only handles a single criterion. INDEX and MATCH overcome all three. The pair works with any column arrangement, can return values from columns to the left of the lookup column, and—when combined with array formulas—can match on multiple criteria simultaneously. This makes it the preferred choice for complex lookups, especially in dashboards and financial models where data isn’t always arranged in a lookup-friendly order.
How INDEX and MATCH Work Together
INDEX returns the value at a given row and column position in a range. MATCH returns the position (row number or column number) of a value within a range. When you nest MATCH inside INDEX, MATCH finds the row (or column) number, and INDEX returns the value at that position. The basic single-criteria formula looks like this:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
For example, to find the price of a product named “Widget” in a table where A2:A100 contains product names and B2:B100 contains prices, you’d write:
=INDEX(B2:B100, MATCH("Widget", A2:A100, 0))
MATCH returns the relative position of “Widget” within A2:A100 (say, row 5), and INDEX returns the value in B5. The “0” in MATCH specifies an exact match, which is almost always what you want for lookups.
Multiple Criteria with Array Formulas
To match on two or more criteria, you need to create a single lookup value by concatenating the criteria with a delimiter, and do the same for the lookup range. The classic array formula is:
=INDEX(return_range, MATCH(1, (criteria1_range=criteria1) * (criteria2_range=criteria2), 0))
In older Excel versions (pre-365), you must press Ctrl+Shift+Enter to enter this as an array formula. In Excel 365 and Excel 2021, it works as a dynamic array formula without the special key combination. Let’s see a concrete example. Suppose you have a table with columns: A=Region, B=Product, C=Sales. You want the sales for “East” region and “Widget” product. The formula would be:
=INDEX(C2:C100, MATCH(1, (A2:A100="East") * (B2:B100="Widget"), 0))
How it works: Each comparison (A2:A100=”East”) returns an array of TRUE/FALSE. Multiplying two such arrays converts TRUE/FALSE to 1/0 and produces a 1 only where both conditions are TRUE. MATCH(1, …, 0) finds the position of the first 1, and INDEX returns the corresponding sales value.
Non-Array Alternative Using CONCATENATE
If you prefer not to use array formulas (or if you’re using an older Excel version that requires Ctrl+Shift+Enter and you find that cumbersome), you can create a helper column that concatenates the criteria into a single string, then use a simple MATCH. For instance, add a column D with the formula:
=A2 & "|" & B2
Then use:
=INDEX(C2:C100, MATCH("East|Widget", D2:D100, 0))
The pipe symbol (|) is a good delimiter because it’s unlikely to appear in your data. This method is often faster for large datasets because it avoids array calculations, and it’s easier for other users to understand. The trade-off is that you need a helper column, which may not be desirable if you’re building a one-off formula or if your data is in an Excel Table that you don’t want to modify.
Handling Text, Numbers, and Dates
Multiple criteria can be numeric, text, or dates. The array formula works the same for all types, but be careful with numbers stored as text. If your criteria are numbers but the lookup column contains text-formatted numbers, MATCH won’t find them. Use the TEXT function to convert the number to text in the formula, or convert the data to real numbers. For dates, ensure both the criteria and the lookup column are actual Excel dates (not text). A common mistake is to type a date as text in the formula, like “2023-01-01”, which won’t match a real date. Instead, use DATE(2023,1,1) or a cell reference containing a date.
Common Errors and How to Fix Them
The most frequent error is #N/A, which means MATCH couldn’t find a 1. This happens when no row meets all criteria. Check for spelling differences, extra spaces, or data type mismatches. Use TRIM and CLEAN functions to remove unwanted spaces and non-printing characters. Another error is #VALUE!, which often occurs when the arrays in the formula are not the same size. For example, if you compare A2:A100 with B2:B50, the multiplication will fail. Always ensure all ranges have the same number of rows. Also, if you forget to press Ctrl+Shift+Enter in older Excel, you might get a #VALUE! error or an incorrect result. If you’re using Excel 365, dynamic arrays handle this automatically, but if you see #SPILL!, it means the formula is trying to return multiple results—check that there are no other values in the cells below.
When to Use INDEX MATCH vs. XLOOKUP
Since Excel 2021, XLOOKUP is the modern replacement for both VLOOKUP and INDEX/MATCH. XLOOKUP can handle multiple criteria natively using the & operator, and it doesn’t require array entry. For example:
=XLOOKUP("East"&"|"&"Widget", A2:A100&"|"&B2:B100, C2:C100)
This is simpler and faster to write. However, INDEX/MATCH is still relevant if you need to use an older Excel version, if you want to avoid the overhead of concatenating ranges (which can slow down large datasets), or if you’re already familiar with the syntax. For most new work, XLOOKUP is recommended, but understanding INDEX/MATCH is still valuable for legacy spreadsheets and for learning how array formulas work.
Frequently Asked Questions
Can I use multiple criteria with INDEX MATCH without pressing Ctrl+Shift+Enter?
Yes, if you use the helper column approach with CONCATENATE, you don’t need array entry. Alternatively, in Excel 365, the array formula works without Ctrl+Shift+Enter because dynamic arrays are the default. In older Excel versions, you must press Ctrl+Shift+Enter for the array formula to work correctly.
Why does my INDEX MATCH return #N/A even though the data exists?
This usually means the criteria don’t exactly match the data. Check for leading/trailing spaces, different number formats (e.g., text vs. number), or case sensitivity (MATCH is case-insensitive, but it won’t ignore spaces). Use TRIM and CLEAN to clean the data, and consider using the EXACT function if you need case-sensitive matching.
Can I use INDEX MATCH to return a value from a column to the left of the lookup column?
Yes, that’s one of the main advantages over VLOOKUP. The return_range can be any column, regardless of its position relative to the lookup_range. For example, if you’re looking up a product name in column C and want the ID from column A, you can use INDEX(A:A, MATCH(…)).
What is the maximum number of criteria I can use?
There’s no hard limit, but practical limits come from Excel’s formula length (8,192 characters) and performance. Each criterion adds a comparison and a multiplication, so very large arrays with many criteria can slow down calculation. In practice, 3-4 criteria are common, and beyond that, you might consider a different approach like Power Query or a database.
Does INDEX MATCH work with wildcards?
MATCH supports wildcards (* and ?) when the match_type is 0 and the lookup_value is text. For example, MATCH(“Wi*”, A2:A100, 0) will find the first cell starting with “Wi”. This works in both single and multiple criteria formulas, but be careful: wildcards only work with text, not numbers.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.