The direct answer: use an array formula like =INDEX(C2:C100, MATCH(1, (A2:A100="North")*(B2:B100="Widget"), 0)) entered with Ctrl+Shift+Enter in older Excel, or the same formula with regular Enter in Excel 365. This returns the value in column C where column A equals “North” and column B equals “Widget”.
Why INDEX MATCH Beats VLOOKUP for Multiple Criteria
VLOOKUP can only look up one value in the leftmost column of a table. When you need to match two or more conditions—say a region and a product—VLOOKUP forces you to create a helper column that concatenates the criteria into a single string. That helper column is fragile: if you change the format of one cell, the concatenation breaks. INDEX MATCH with multiple criteria avoids the helper column entirely by building an array of TRUE/FALSE tests and finding the first row where all tests pass.
Another reason to prefer INDEX MATCH: it can return a value from any column to the left or right of the lookup column, and it does not slow down noticeably when you insert or delete columns. VLOOKUP’s column index number becomes wrong when you add a column, but INDEX MATCH references the actual return range, so it stays correct.
How the Array Formula Works
Assume your data is laid out with region in A2:A20, product in B2:B20, and sales in C2:C20. The formula =INDEX(C2:C20, MATCH(1, (A2:A20="North")*(B2:B20="Widget"), 0)) works in three steps:
(A2:A20="North")produces an array of TRUE/FALSE values. In Excel, TRUE equals 1 and FALSE equals 0 when used in arithmetic.- Multiplying the two arrays with
*gives 1 only where both conditions are TRUE, and 0 everywhere else. So the product array is a list of 1s and 0s. MATCH(1, product_array, 0)finds the position of the first 1 in that array.INDEXthen returns the value in column C at that same position.
In Excel 365 and Excel 2021, you can enter this normally because dynamic arrays handle the intermediate arrays automatically. In Excel 2019 and earlier, you must press Ctrl+Shift+Enter to enter it as an array formula—you will see curly braces {} appear around the formula in the formula bar. On a Mac, the shortcut is Control+Shift+Return.
Worked Example with Real Numbers
Suppose you have this small table in cells A1:C4:
- A1: Region, B1: Product, C1: Sales
- A2: North, B2: Widget, C2: 100
- A3: South, B3: Widget, C3: 150
- A4: North, B4: Gadget, C4: 200
To find sales for North Widget, use =INDEX(C2:C4, MATCH(1, (A2:A4="North")*(B2:B4="Widget"), 0)). The product array is (TRUE*TRUE, FALSE*TRUE, TRUE*FALSE) which becomes (1, 0, 0). MATCH finds position 1, so INDEX returns C2, which is 100. If you change the criteria to South Widget, the product array becomes (0, 1, 0), MATCH returns 2, and the result is 150.
Using AND Logic vs. OR Logic
The multiplication * acts like an AND: all conditions must be TRUE. If you need OR logic—for example, find a sale where region is North OR product is Widget—you should use addition + instead, but then you must check that the sum is greater than 0, not equal to 1. The formula becomes =INDEX(C2:C4, MATCH(TRUE, (A2:A4="North")+(B2:B4="Widget")>0, 0)). However, OR logic is less common because it can return the first row that meets any condition, which may not be what you expect. For most multi-criteria lookups, AND logic is what you want.
Alternative: XLOOKUP with Multiple Criteria (Excel 365)
If you have Excel 365 or Excel 2021, XLOOKUP offers a cleaner syntax. The equivalent formula is =XLOOKUP(1, (A2:A4="North")*(B2:B4="Widget"), C2:C4). XLOOKUP handles arrays natively, so you do not need Ctrl+Shift+Enter. It also lets you specify a not-found message with a fourth argument, like =XLOOKUP(1, (A2:A4="North")*(B2:B4="Widget"), C2:C4, "Not found"). XLOOKUP is faster to write and easier to read, but INDEX MATCH still works in every Excel version from 2007 onward, making it the safer choice for shared workbooks.
Common Problems and How to Fix Them
- #N/A error: No row matches all criteria. Check for extra spaces, text vs. number mismatches, or that your criteria ranges are the same size.
- Wrong result with duplicates: MATCH returns the first match only. If you need the last match, use
MATCH(2, ...)with1/(condition)trick, but that is more advanced. For a sum of all matches, use SUMIFS instead. - Array formula not working: In older Excel, if you press Enter instead of Ctrl+Shift+Enter, the formula returns a #VALUE! error. Re-enter with the correct shortcut.
- Criteria ranges not locked: When copying the formula down, use absolute references like
$A$2:$A$20to keep the lookup ranges fixed.
When to Use SUMIFS or FILTER Instead
INDEX MATCH returns a single value. If you need to sum all sales that match multiple criteria, use =SUMIFS(C2:C20, A2:A20, "North", B2:B20, "Widget"). If you need to return all matching rows as a list, use the FILTER function in Excel 365: =FILTER(A2:C20, (A2:A20="North")*(B2:B20="Widget")). FILTER spills multiple rows automatically. For counting matches, use COUNTIFS. Each function has a specific purpose—INDEX MATCH is best when you want one specific value from a known column.
Frequently Asked Questions
Can I use INDEX MATCH with three or more criteria? Yes. Just multiply more condition arrays inside the MATCH, for example (A2:A20="North")*(B2:B20="Widget")*(C2:C20="Large"). The logic is the same: the product is 1 only when all conditions are TRUE.
Does the order of criteria matter? No, because multiplication is commutative. But the order of rows in your data matters—MATCH returns the first row that satisfies all criteria, so if there are duplicates, you get the topmost one.
Why do I get #VALUE! instead of #N/A? #VALUE! usually means you did not enter the formula as an array formula in older Excel, or your criteria ranges have different sizes (e.g., A2:A20 and B2:B10). Make sure all ranges have the same number of rows.
Can I use cell references for criteria? Yes, instead of typing “North” inside quotes, use a cell reference like $E$1. The formula becomes =INDEX(C2:C20, MATCH(1, (A2:A20=$E$1)*(B2:B20=$F$1), 0)). This makes it easy to change criteria without editing the formula.
Is INDEX MATCH faster than VLOOKUP? In large datasets, INDEX MATCH is often faster because VLOOKUP reads the entire table, while INDEX MATCH only reads the lookup and return columns. The difference is noticeable on tens of thousands of rows, but for small tables it is negligible.
Related
- Excel: How to Hide Rows in a Table (3 Ways)
- Excel Split Cells Fast: 4 Ways That Work
- Excel: How to Count X’s in a Column
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Listing Items: 5 Real MethodsThe fastest way to list items in Excel is to use the =UNIQUE(range) formula to extract distinct values,…
- Date Filter Formula in Excel: Examples and TipsIn Excel for Microsoft 365, use the FILTER function to return rows whose dates meet your criteria. For…
- Calendar Math in Excel: Dates, Workdays, and FormulasCalendar math in Excel means using real date values and date functions to add or subtract days, find…
- Excel Formula to Find and Replace Text in Cells: ExamplesTo replace text inside a cell with an Excel formula, use SUBSTITUTE. If the original text is in…
- Rank Values in Excel: Formulas, Ties, and Sorting ExplainedYes. In Excel for Microsoft 365 on Windows, use RANK.EQ to assign a rank to a value, or…
- Excel SUMIFS With Multiple Criteria: A Practical GuideWith sales in D2:D7, regions in B2:B7, and products in C2:C7, use =SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Tea") to add sales where the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.