Excel INDEX MATCH with Multiple Criteria: Real Example

Formulas & functions · Updated November 22, 2026

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:

  1. (A2:A20="North") produces an array of TRUE/FALSE values. In Excel, TRUE equals 1 and FALSE equals 0 when used in arithmetic.
  2. 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.
  3. MATCH(1, product_array, 0) finds the position of the first 1 in that array. INDEX then 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, ...) with 1/(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$20 to 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.