Excel guide · 5 min read
How to use INDEX MATCH with multiple criteria
=INDEX(D2:D9,MATCH(1,(A2:A9=H1)*(B2:B9=H2),0)). Each condition returns TRUE or FALSE for every row, multiplying them gives 1 only where all conditions are true, and MATCH finds the first 1. In Excel 365 and 2021 it works with Enter; in Excel 2019 and older press Ctrl+Shift+Enter, or use a helper column that joins the columns into one key.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Color | Price | Key | Product | Hoodie | |
| 2 | T-shirt | M | Navy | 14 | T-shirt|M|Navy | Size | M | |
| 3 | T-shirt | L | Navy | 15 | T-shirt|L|Navy | Color | Navy | |
| 4 | Hoodie | S | Grey | 32 | Hoodie|S|Grey | |||
| 5 | Hoodie | M | Grey | 34 | Hoodie|M|Grey | |||
| 6 | Hoodie | M | Navy | 36 | Hoodie|M|Navy | |||
| 7 | Hoodie | L | Navy | 38 | Hoodie|L|Navy | |||
| 8 | Cap | One size | Navy | 12 | Cap|One size|Navy | |||
| 9 | Hoodie | XL | Grey | 40 | Hoodie|XL|Grey |
Download this sheet as an Excel practice file (.xlsx)
The formula: MATCH(1, (condition)*(condition), 0)
A merch shop's price list has the same product in several sizes and colours. You cannot look up "Hoodie" alone, because there are five hoodies. You need the row where product is Hoodie and size is M.
(A2:A9=H1) checks every product against Hoodie and returns a list of TRUE and FALSE. (B2:B9=H2) does the same for size. Multiplying two lists turns TRUE into 1 and FALSE into 0, and 1*1 is the only way to get 1. So the result is a column of 0s with a 1 on each row that meets both conditions. MATCH(1, ..., 0) returns the position of the first 1, and INDEX returns the price from that row.
Result: 34
34, the Grey hoodie in M. It is the first of two rows that match, which is why a third condition is usually needed.
Add a third condition
There are two medium hoodies, Grey and Navy. MATCH stops at the first match, so the formula above returns the Grey price even if the customer wants Navy. That is not an error and nothing warns you, which is the most dangerous gotcha with this formula.
Add one more (range=value) bracket for every condition. Before trusting a result, check how many rows match with =COUNTIFS(A2:A9,H1,B2:B9,H2). If it says more than 1, you need another condition or you need FILTER to see all of them.
Result: 36
36, the Navy hoodie in M.
Older Excel: avoid Ctrl+Shift+Enter
In Excel 2019 and older, the formula above must be entered with Ctrl+Shift+Enter. Pressed with plain Enter, it usually returns #N/A or #VALUE!, and the next person who edits the cell and presses Enter breaks it again.
Wrapping the conditions in an extra INDEX(...,0) makes Excel evaluate the array without the special key. It looks odd, but it is a well-known trick and works in every version.
Result: 36
36, with a normal Enter in any version of Excel.
The safer layout: a helper key column
Column E joins product, size and colour into one text key, such as Hoodie|M|Navy, with =A2&"|"&B2&"|"&C2 copied down. Then a plain MATCH on one column does the job. It needs no array maths, is fast on 100,000 rows, and anyone can see why a row matched or did not.
Always put a separator between the parts. Without one, two different rows can produce the same key: product code "AB" with size "12" and product "A" with size "B12" both join to AB12. A character that never appears in the data, like |, prevents that.
Result: 36
36. MATCH finds Hoodie|M|Navy in E6.
#N/A when one condition misses
If no row meets every condition, MATCH has no 1 to find and returns #N/A. There is no hoodie in XS, so asking for one fails, even though both "Hoodie" and sizes exist on their own.
Wrap the formula in IFNA to show a message instead. Use IFNA rather than IFERROR so that real mistakes, such as a typo in a range, still show up. If you get #N/A for a combination you can see in the list, check for trailing spaces with LEN, or a size typed as the number 10 in one place and the text "10" in another.
Result: Not stocked
Not stocked, instead of #N/A.
Excel 365: XLOOKUP does the same in one step
XLOOKUP accepts the same multiplied conditions as its lookup array, so you look up 1 directly and can put the not-found message in the fourth argument. It works with Enter in Excel 365 and Excel 2021 and later.
If several rows match and you need all of them, use FILTER instead: =FILTER(C2:D9,(A2:A9=H1)*(B2:B9=H2)) spills both medium hoodies with their colours and prices (Grey 34, Navy 36).
Result: 36
36.
Which one should you use?
On Excel 365 or 2021 with a modest list, use XLOOKUP with multiplied conditions: it is the shortest and has a built-in not-found value. When the file must open in Excel 2019 or older, use INDEX/MATCH with the INDEX(...,0) wrapper.
For large tables, shared files, or anything you will debug later, use the helper key column. It is the fastest and the easiest to check. If the value you return is a number and each combination is unique, =SUMIFS(D2:D9,A2:A9,H1,B2:B9,H2,C2:C9,H3) also works, but it adds duplicates together instead of warning you about them.
Common mistakes
Using + instead of *
Adding the conditions means OR: a row only has to meet one of them. =INDEX(D2:D9,MATCH(1,(A2:A9=H1)+(B2:B9=H2),0)) returns the first medium item, a T-shirt. Multiply for AND.
Pressing only Enter in Excel 2019 or older
Use Ctrl+Shift+Enter, or the MATCH(1,INDEX(...,0),0) version that needs no special key.
Not noticing duplicate matches
MATCH silently returns the first match. Check with COUNTIFS that each combination occurs once, or add another condition.
Ranges of different sizes
Every range in the conditions and the INDEX range must cover the same rows, such as A2:A9, B2:B9 and D2:D9. Otherwise you get #VALUE! or #N/A.
FAQ
How do I do INDEX MATCH with two criteria?
Use =INDEX(return_range,MATCH(1,(range1=value1)*(range2=value2),0)). In Excel 2019 and older, confirm it with Ctrl+Shift+Enter or wrap the conditions in INDEX(...,0).
Why does my INDEX MATCH with multiple criteria return #N/A?
No row meets all the conditions at once. That can be genuine, or caused by a trailing space, a number stored as text, or an array formula entered without Ctrl+Shift+Enter in older Excel. Check each condition on its own with COUNTIF.
Can I use INDEX MATCH with criteria in rows and columns?
Yes. Use one MATCH for the row and another for the column: =INDEX(B2:E9,MATCH(H1,A2:A9,0),MATCH(H2,B1:E1,0)). That is a two-way lookup, which is different from several conditions on the same row.
Is XLOOKUP better than INDEX MATCH for multiple criteria?
It is shorter and has a built-in not-found argument, but it only exists in Excel 365 and Excel 2021 and later. INDEX/MATCH works in every version.
How do I return every row that matches multiple criteria?
INDEX/MATCH and XLOOKUP only return the first match. In Excel 365, use FILTER, such as =FILTER(C2:D9,(A2:A9=H1)*(B2:B9=H2)), to spill every matching row.
Functions used
INDEXValue at a positionMATCHPosition of a valueXLOOKUPModern lookup in any directionIFNAFallback for #N/A onlyFILTERKeeps matching rows, spillsCOUNTIFSCounts with several conditionsSUMIFSAdds with several conditions
Related guides
- How to use INDEX MATCH in Excel
- XLOOKUP with multiple criteria
- INDEX MATCH vs XLOOKUP
- How to do a two-way lookup in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to look up which range a number falls in
- MAX IF in Excel: find the largest value that meets a condition
Updated