Contextures

Excel Lookup Multiple Criteria

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.

Excel Lookup With Two Criteria

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

Lookup Functions

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.

lookup multiple criteria

INDEX and MATCH

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

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

Simple INDEX and MATCH

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))

indexmatchprice01

How INDEX MATCH Formula Works

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

Match for Multiple Criteria

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.

multiple criteria

MATCH True or False

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

check for matches

MATCH Both True

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.

multiply true and false values

  ▲TOP

Lookup With Multiple Criteria

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.

array entered formula

How the Complex Formula Works

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

select part of the formula

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

select part of the formula

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

select MATCH part of the formula

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.

see MATCH result

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

INDEX MATCH formula result

Download the Sample File

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

More Tutorials

INDEX / MATCH Functions

Functions List

Compare Lookup Functions

VLOOKUP

30 Functions in 30 Days

 

 

Get weekly Excel tips from Debra

 

 

 

excel tools

 

Last updated: May 10, 2018 1:12 PM