To use INDEX MATCH with multiple row criteria in Excel, combine the MATCH function with an array expression that checks each row against all criteria, typically using =INDEX(return_range, MATCH(1, (criteria_range1=criteria1) * (criteria_range2=criteria2), 0)) entered as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365). This returns the first row that meets every condition.
Why INDEX MATCH Instead of VLOOKUP?
INDEX MATCH is a classic alternative to VLOOKUP because it can look left, handle columns to the right without rearranging data, and is less prone to breaking when columns are inserted. With multiple criteria, VLOOKUP requires a helper column that concatenates the criteria, which adds clutter and maintenance. INDEX MATCH keeps the logic in the formula, making it more transparent and easier to audit. It also works with unsorted data, whereas VLOOKUP’s approximate match requires sorting.
How the Array Formula Works
The core idea is to build a TRUE/FALSE array for each criterion, multiply them together (TRUE*TRUE=1, any FALSE=0), and then MATCH the value 1 in that array to find the row number. The INDEX function then retrieves the value from that row in the return column.
For example, assume you have a table with:
- Product in A2:A20
- Region in B2:B20
- Sales in C2:C20
You want the sales for Product “Widget” in Region “East”. Place the criteria in cells E2 and F2. The formula in G2 is:
=INDEX(C2:C20, MATCH(1, (A2:A20=E2) * (B2:B20=F2), 0))
In Excel 365, enter normally. In older Excel (2019 and earlier), press Ctrl+Shift+Enter to create an array formula (curly braces appear). The result is the sales figure from the first row where both conditions are true. If no row matches, you get #N/A.
Using Boolean Logic for Multiple Criteria
The multiplication operator (*) acts as AND logic. For OR logic, use addition (+), but be careful: if you add, a row where both criteria are true will produce 2, not 1, so MATCH would fail. To handle OR correctly, wrap each condition in IF or use --(condition) to convert to 0/1 and then add, but the simplest is to use the SIGN function or just test for >0. However, for most multiple-row criteria, AND is what you need.
You can also use comparison operators like >= or <> inside the criteria. For example, to find sales where quantity is greater than 100 and region is “West”: (C2:C20>100) * (B2:B20="West").
Using the FILTER Function (Excel 365)
If you use Excel 365 or Excel 2021, the FILTER function is a more straightforward alternative. It returns all matching rows, not just the first one. For the same example, to get all sales for Widget in East:
=FILTER(C2:C20, (A2:A20="Widget") * (B2:B20="East"))
This spills the results down automatically. If you only want the first match, wrap in INDEX: =INDEX(FILTER(C2:C20, (A2:A20="Widget") * (B2:B20="East")), 1). FILTER is easier to read and doesn’t require array entry, but it’s only available in dynamic array Excel versions.
Handling #N/A and Errors
If no row meets all criteria, MATCH returns #N/A. To display a friendly message, wrap the formula in IFERROR:
=IFERROR(INDEX(C2:C20, MATCH(1, (A2:A20=E2) * (B2:B20=F2), 0)), "No match found")
Remember that in older Excel, you must still enter this as an array formula. Another common issue is trailing spaces in criteria cells—use TRIM on the criteria or the lookup ranges to avoid false mismatches.
Alternative: SUMPRODUCT for Numeric Results
If the value you want to return is numeric and you expect only one match, SUMPRODUCT can work without array entry:
=SUMPRODUCT((A2:A20="Widget") * (B2:B20="East") * C2:C20)
This sums all matching sales, which is fine if there’s only one. But if multiple rows match, it sums them, which may not be what you want. For text results, SUMPRODUCT won’t work directly. So INDEX MATCH remains the flexible choice for any return type.
Frequently Asked Questions
Do I need to press Ctrl+Shift+Enter? In Excel 365 and Excel 2021, dynamic arrays mean you just press Enter. In Excel 2019 and earlier, you must press Ctrl+Shift+Enter after typing the formula, otherwise it returns #VALUE! or an incorrect result. If you see curly braces in the formula bar, it’s an array formula.
Can I use multiple criteria across different sheets? Yes, just include the sheet name in the ranges, e.g., Sheet2!A2:A20. The formula works the same way as long as the ranges are the same size.
What if I want to match on row and column criteria (two-dimensional lookup)? That’s a different scenario: you use two MATCH functions inside INDEX, one for row and one for column. For example, =INDEX(C2:E20, MATCH(H2, A2:A20, 0), MATCH(I2, C1:E1, 0)).
Why does my formula return #VALUE! instead of a result? This usually means you didn’t enter it as an array formula in older Excel, or you have mismatched range sizes (e.g., A2:A20 and B2:B21). Ensure all criteria ranges have the same number of rows.
Can I use wildcards with multiple criteria? Yes, but only with text. Use ISNUMBER(SEARCH("partial", range)) to do a contains-match, or use LEFT(range,3)="ABC" for prefix. These return TRUE/FALSE, so they work in the multiplication.
Is there a limit to how many criteria I can use? No hard limit, but performance degrades with very large datasets (tens of thousands of rows). For huge data, consider using Power Query or a pivot table instead.
Related
- How to Unmerge Cells in Excel (Group of Cells)
- Excel HR Templates for Attendance: A Practical Guide
- Excel How to Ungroup Cells: Rows, Columns & Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Partial Text: Match Words with WildcardsYes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the…
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
- How to Put an XLOOKUP Formula in Excel: Step-by-StepTo put an XLOOKUP formula in Excel, select the cell where you want the answer, then enter a…
- Add a Sequence in Excel with the SEQUENCE FunctionTo add a numbered sequence in Excel, enter =SEQUENCE(10) in a blank cell to spill the numbers 1…
- Can’t Filter in Excel? Causes, Fixes, and WorkaroundsYes—Excel can filter a list or table, but the Filter command may be unavailable, the dropdown arrows may…
- Postgres Function Return Table? ExplainedYes, a PostgreSQL function can return a table by using RETURNS TABLE (...) in its definition, which lets…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.