Check Multiple Criteria with Excel INDEX and MATCH

Article from: http://blog.contextures.com/archives/2012/07/12/check-multiple-criteria-with-excel-index-and-match/

The INDEX function can return a value from a range of cells, and the MATCH function can calculate a value's position in a range of cells.

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.

=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))

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:

=INDEX($D$2:$D$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))

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.

• (A13=$B$2:$B$10)*(B13=$C$2:$C$10)

The MATCH function looks for the 1 in the array of results.

• MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0)

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.

=INDEX($A$2:$A$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))

In this example, the product code would be JK003, from cell A9.