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.
See how to do an Excel lookup with 2 criteria. using either:
To help you get started, there are 2 videos below, and 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.
To help you decide which solution to use for an Excel lookup with multiple criteria, here are the key differences between them.
To do this 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.
Before using INDEX and MATCH with multiple criteria, let's see how they work together in a simple formula.
In this example, we need to find “Sweater” in a column B of a price list, and get its price from column C.
Here’s how that simple INDEX / MATCH formula finds the sweater price:
So, by combining INDEX and MATCH, you can find the row location of an item, and return the price from that row.
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.
The price for a large jacket is 40, so that should be the result if an INDEX/MATCH formula is entered in cell E13.
To calculate the price based on 2 criteria, enter this array-entered* INDEX and MATCH formula in cell E13. The formula is explained below.
*Array-entered -- 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.
Product prices are in cells E2:E10, and INDEX will return a Price from that range.
The MATCH function tells INDEX which row to use in that range
1) In the MATCH function, the first argument, lookup_value, is 1
2a) For the second argument, lookup_array, there are 2 tests:
2b) Next, those TRUE and FALSE results are multiplied, to return zeros and ones
2c) In this example, only the 8th product matches both criteria, Jacket and Large, and returns a 1
3) The third argument in MATCH, match_type, is zero, to return an exact match.
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
Based on that result, INDEX returns the price for the 8th product in the price lookup table.
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 columns for Code, Item, Size and Price.
At the top of the sheet, two criteria have been entered:
Based on those criteria, we need two results - Price and Code.
The FILTER function has 3 arguments -- array, include, and if_empty (optional)
For the array argument, we need a result from the Price column:
For the include argument, the formula must check the Item column, to see if it matches cell A2
AND the formula must check the Size column, to see if it matches cell B2
Both criteria are in the include argument, with the Multiply operator (*) between them
=FILTER(tblProducts[Price], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))
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))
Last updated: March 2, 2021 8:51 PM