Check Multiple Criteria with Excel INDEX and MATCH
The INDEX function can return a value from a specific place in a list, and the MATCH function can find something in a list. Use INDEX and MATCH together, for a flexible and powerful lookup formula. It’s similar to a VLOOKUP, but the item that you’re looking for doesn’t have to be in the column at the left.
Get an Item’s Price with INDEX and MATCH
For example, find “Sweater” in a column B of a price list, and get its price from column C. In the screen shot below, cell A7 has the name of the item that we need a price for – Sweater.
We can enter an INDEX and MATCH formula in cell C7, to get the price for that item:
How the INDEX and MATCH Formula Works
Here’s how that formula finds the sweater price:
- 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 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 INDEX and MATCH 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 INDEX and MATCH 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
Learn more about Lookup, Information and Text functions in the 30 Excel Functions in 30 Days kit.
For more tips and examples, and to download the INDEX and MATCH sample file, visit the INDEX function and MATCH function page on the Contextures website. This is Example 4 in the sample file section of that page.