How to do an Excel lookup with multiple criteria in columns, with the INDEX and MATCH functions. Get the sample file, then watch the step-by-step video, or follow the written instructions.

This video shows how the INDEX and MATCH functions work together, with one criterion. Then, the formula is changed, to work with multiple criteria.

To follow along with the video, download the sample file. There are written instructions too, in the next section.

- 0:00 Introduction
- 0:26 Lookup with One Criterion
- 1:52 Test Each Criterion
- 2:22 Test With a Formula
- 3:26 Multiply the Results
- 4:03 INDEX / MATCH Formula
- 5:20 Check the Formula
- 5:57 Get the Sample File

There are many ways to do a simple lookup in Excel, using functions such as VLOOKUP or HLOOKUP. In this example, we need to do a complex lookup:

- there are multiple criteria, instead of just one
- we need to find a product code, which is to the left of the criteria

VLOOKUP won't work here, so we'll use the INDEX and MATCH functions together, to get the results that we need.

To do this complex lookup with multiple criteria, we'll use the INDEX and MATCH functions.

- The INDEX function can return a value from a specific place in a list
- The MATCH function can find the location of an item in a list.

When INDEX and MATCH are used together, they create a flexible and powerful lookup formula. **▲TOP**

Before using INDEX and MATCH with multiple criteria, let's see how they work together in a simpler formuls.

In this lookup formula, we need to find “Sweater” in a column B of a price list, and get its price from column C.

- The item name that we need a price for is entered in cell A7 – Sweater.
- This INDEX and MATCH formula is entered in cell C7, to get the price for that item:

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

Here’s how that simple INDEX / MATCH 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. **▲TOP**

In the previous example, the match was based on one criterion -- the Item name. For the next lookup, there are 2 criteria -- Item name and product Code.

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.

In the lookup formula, we need the MATCH function to check both the Item and Size columns.

To show how that will work, I'll add temporary columns on the worksheet, to check the item and size columns -- is the item a Jacket, and is the Size a Large?

Enter this formula in F2, and copy down to F10: **=C2=$C$13**

- If the Item in column C is a Jacket, the result in column E is TRUE. If not, the result is FALSE

Enter this formula in G2, and copy down to G10: **=D2=$D$13**

- If the Size in column D iis Large, the result in column F is TRUE. If not, the result is FALSE

We need the price from the row where both results are TRUE. We'll use a formula to calculate that for us:

Enter this formula in H2, and copy down to H10: **=F2*G2**

In Excel, TRUE is equal to 1, and FALSE is equal to zero. When you multiply the values,

- If
**either**value is FALSE (0), the result is zero - If
**both**values are TRUE (1), the result is 1

Only the 8th row in our list of items has a 1, because both values are TRUE in that row.

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.

But, instead of adding extra columns to the worksheet, we will use an array-entered INDEX and MATCH formula to do all the work.

Here is the **array-entered*** formula that we’ll use in cell E13, to get the correct price:

**=INDEX(E2:E10,**

MATCH(1,

(C13=C2:C10)*(D13=D2:D10),0))

*Press **Ctrl + Shift + Enter**, instead of just pressing the Enter key. That will automatically add curly brackets around the formula.

Here's how this INDEX MATCH multiple criteria formula works. In this INDEX and MATCH example,

- prices are in cells
**E2:E10**, and the INDEX function will return a value from that range. - item name criterion is in cell
**C13** - size criterion is in cell
**D13**.

The formula checks for the selected item name in **$C$2:$C$10**, and the selected size in **$D$2:$D$10**. The TRUE and FALSE results are multiplied, to return zeros and ones.

- (
**C13**=**$C$2:$C$10**)*(**D13**=**$D$2:$D$10**)

To see how that works, select this part of the formula in the formula bar

Then, press the F9 key, to see the calculated results for the selected potion of the formula. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is

Next, select the MATCH function portion of the formula in the formula bar

Press F9, to see the result. The MATCH function looks for the **1 **in the array of results, so in our formula, it returns 8.

Then, **press the ESC key**, to exit the function without saving the calculations.

As the final step in the formula, the INDEX function returns the price from the 8th data row in column E (cell E9). **▲TOP**

To follow along with this tutorial, download the zipped sample Lookup with Multiple Criteria file. The workbook is in xlsx format, and does not contain any macros. **▲TOP**

Last updated: October 15, 2018 12:56 PM