Check Multiple Criteria with Excel INDEX and MATCH
For example, in the screen shot below, cell A7 contains the item name, Sweater:
- the MATCH function can find “Sweater” in the range B2:B4. The result is 1, because “Sweater” is in the first row of that range.
- the INDEX function can tell you that in the range C2:C4, the first row contains the value 10.
So, by combining INDEX and MATCH, you can find the row with “Sweater” and return the price from that row.
Find a Match for Multiple Criteria
In the previous example, the match was based solely on the Item name – Sweater. Sometimes life, and Excel workbooks, are more complicated.
In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you’ll need to specify both the item name and the size. We want to find the price for a large jacket.
Does it MATCH? True or False
Instead of a simple MATCH formula, we’ll use one that checks both the Item and Size columns. To do something similar on a worksheet, we could add columns to check the item and size columns.
- If the Item in column B is a Jacket, the result in column E is TRUE. If not, the result is FALSE
- If the Size in column C is Large, the result in column F is TRUE. If not, the result is FALSE
In column G, when you multiply the TRUE/FALSE values, the result is 1, only if both are TRUE.
We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.
Use MATCH With Multiple Criteria
Instead of adding extra columns to the worksheet, we can use an array-entered formula to do all the work. Here is the formula that we’ll use to get the correct price, and below is the explanation:
NOTE: This is an array-entered formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.
In this example,
- prices are in cells D2:D10, so that is the range that the INDEX function will use.
- item name is in cell A13
- size is in cell B13.
The formula checks for the selected items in $B$2:$B$10, and sizes in $C$2:$C$10. The results are multiplied.
The MATCH function looks for the 1 in the array of results.
If you select that part of the formula and press the F9 key, you can see the calculated results. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is 1.
So, the INDEX function returns the price – 40 – from the 8th data row in column D (cell D9).
To find the product code for the selected item and size, you would change the formula to look in cells A2:A10, instead of the price column.
In this example, the product code would be JK003, from cell A9.
More INDEX and MATCH Examples
For more tips and examples, and to download the sample file, please visit the INDEX and MATCH functions page on the Contextures website. This is Example 4 in the sample file.