Contextures

Excel Lookup Multiple Criteria

How to do an Excel lookup with 2 or more criteria in columns, with the FILTER function (Office 365), or the INDEX and MATCH functions. Get the sample file, then watch the step-by-step video, or follow the written instructions.

Excel Lookup With 2 Criteria

There are many ways to do an Excel lookup based on 2 criteria. On this page, you'll see how to do an Excel lookup with 2 criteria. using either:

  1. INDEX/MATCH functions (all Excel versions)
  2. or the FILTER function (Excel 365)

The next section will help you choose which option will work better for you, and then two short videos will show you how each option works.

After you choose a solution, there are detailed steps below, on how to set up either the FILTER function, or INDEX and MATCH.

Should You Use FILTER or INDEX/MATCH ?

To help you decide which solution to use for a Microsoft Excel lookup with multiple criteria, here are the key differences between them.

compare FILTER and INDEX/MATCH

INDEX/MATCH Functions

  • These two functions are available in all versions of Excel
  • If there are multiple results for the criteria, the first result from the range is returned
  • The multi-criteria formula must be array entered, unless Excel version has dynamic arrays

FILTER Function

  • This function is only available if your version of Excel has dynamic arrays (Office 365). Be sure that other people who need to use the workbook also have dynamic arrays
  • If there are multiple results for the criteria, the results will spill down to the rows below, to show all of the items.
  • Does not need to be array-entered

Other Solutions

Also, there are other ways to do a Microsoft Excel lookup with multiple criteria. Take a look at the examples on the following pages:

-- Excel LOOKUP function for Multiple Criteria

-- Excel VLOOKUP formula for 2-column lookup

Also, see more information about Excel's different lookup functions.

Videos: Excel Lookup With 2 Criteria

To help you get started, there are 2 videos below, that show you how each lookup option works. There are written steps below the videos:

1) Excel Lookup with Multiple Criteria: Shows how the INDEX and MATCH functions work together, with one criterion. Next, at the 1:50 mark, the formula is changed, to work with 2 criteria. To follow along with the video, download the sample file.

2) Get Started with Excel FILTER function: Shows how the FILTER function works, with one criterion. The 3rd example at the 6:00 mark, shows the FILTER function with 2 criteria. To follow along with the video, download the sample file.

INDEX and MATCH

To do an Excel lookup with multiple criteria, you can use the INDEX and MATCH functions.

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

Simple INDEX and MATCH

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

In the following formula, we need to find “Sweater” in a column B of a price list, and get its price from column C on the same worksheet.

  • The Item criterion 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))

The formula returns the correct price for the sweater - 10.

indexmatchprice01

How Simple INDEX MATCH Formula Works

Here’s how that simple INDEX / MATCH formula finds the correct price for sweater.

  1. First, the MATCH function finds the location of "Sweater" in cells B2:B4, and returns that number
  2. Next, the INDEX function returns the price from that row, in cells C2:C4

So, by combining INDEX and MATCH, you can find the row location of an item, and return the price from that row.

Here are the function details:

1) MATCH

The MATCH function has 3 arguments:

  • lookup_value: What value do you want to find in the lookup array?
  • lookup_array: Where is the lookup array?
  • [match_type]: (optional) Should MATCH find an exact match (0) for the lookup value, or a close match (1 or -1)?

In this example, the MATCH function looks for the value from cell A7, in the range B2:B4, with an exact match.

  • The result is 1, because "Sweater" is in the first row of that range.
2) INDEX

The INDEX function has 3 arguments:

  • array: Where is the array?
  • row_num: Which row has the value you want returned?
  • [column_num]: (optional) Which column has the value you want returned?

In this example, the INDEX function returns 10 -- the price from row 1, in the range C2:C4

INDEX/MATCH 2 Criteria

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

In this pricing lookup table, each item is listed 3 times - once for each size. We want to find the price for a specific Item and Size.

  • Item: Jacket - entered in cell C13
  • Size: Large - entered in cell D13

The price for a large jacket is 40, so that should be the result if an INDEX/MATCH formula is entered in cell E13.

multiple criteria

INDEX/MATCH Formula 2 Criteria

To calculate the price based on 2 criteria, enter this array-entered* INDEX and MATCH formula in cell E13. The formula is explained below.

Note: In Excel 365, which has spill functions, you can just press Enter

  • =INDEX(E2:E10,
    MATCH(1,
    (C13=$C$2:$C$10) * (D13=$D$2:$D$10),0))

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

array entered formula

How the 2 Criteria Formula Works

Here's how this INDEX MATCH multiple criteria formula works.

INDEX Function

Product prices are in cells E2:E10, and INDEX will return a Price from that range.

  • =INDEX(E2:E10,

MATCH Function - Find the Row

The MATCH function tells INDEX which row number to use, in that range.

Note: This number might be different from the worksheet row number.

1) In the MATCH function, the first argument, lookup_value, is 1

  • MATCH(1,

2a) For the second argument, lookup_array, there are 2 tests:

  • is Jacket in the Item column - TRUE (1) or FALSE (0)
    • (C13=$C$2:$C$10)
  • is Large in the Size column - TRUE or FALSE
    • (D13=$D$2:$D$10)

2b) Next, those TRUE and FALSE results are multiplied, to return zeros and ones

  • multiplying true and false

2c) In this example, only the 8th product matches both criteria, Jacket and Large, and returns a 1

  • If the lookup_array argument is evaluated in the formula bar, there is a 1 in the 8th position of the array.
  • select part of the formula

3) The third argument in MATCH, match_type, is zero, to return an exact match.

INDEX/MATCH Result

So, the result of the MATCH function is 8, because it found an exact match for the the lookup value, 1, in the 8th position in the lookup array

  • see MATCH result

Based on that result, INDEX returns the price for the 8th product in the price lookup table.

INDEX MATCH formula result

FILTER Function

If your version of Excel has dynamic arrays (Office 365), you can use the new FILTER function to return the results that you need. The FILTER function lets you return results from a range, based on your criteria.

In this example, there is a named table, tblProducts, with 4 columns -- Code, Item, Size and Price.

product list in named table

Criteria Cells

At the top of the sheet, two criteria have been entered, for the product specifications

  • cell A2 - the item name for the product: ┬áJacket
  • cell B2 - the item size for the product: Large

Based on those criteria, we need two results - the product's Price and the product's alphanumeric Code.

criteria cells at top of worksheet

Get Product Price with FILTER Function

First, in cell C2, we'll enter a FILTER formula to calculate the product price, based on the criteria in cells A2 and B2.

The FILTER function has 3 arguments:

  1. array
  2. include
  3. if_empty (optional)

FILTER function has 3 arguments

1) Array Argument

Product prices are stored in this range:

  • in the table named tblProduct
  • in the column named Price.

So, to start the formula,

  • Type an equal sign: =
  • Type the function name, FILTER
  • Type the opening bracket - (
  • On the worksheet, click at the top of the heading cell in the Price column

Excel will automatically enter a reference to the Price column, in a structured table reference.

The formula should look like this:

  • =FILTER(tblProducts[Price],

2) Include Argument

For the include argument, the formula must check the Item column, to see if it matches cell A2

  • (tblProducts[Item]=A2)

AND the formula must check the Size column, to see if it matches cell B2

  • (tblProducts[Size]=B2)

Both criteria are in the include argument, with the Multiply operator (*) between them

=FILTER(tblProducts[Price], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))

FILTER formula for product price

Product Code with FILTER

The FILTER formula to return the product code is almost the same, but for the array argument, we need a result from the Code column:

=FILTER(tblProducts[Code], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))

FILTER formula for product code

Get the Sample File

More Tutorials

INDEX / MATCH Functions

FILTER function examples

Compare Lookup Functions

VLOOKUP

 

Last updated: March 4, 2022 1:35 PM