Home > Formulas > Lookup > Multiple Criteria Excel Lookup Multiple CriteriaNo more struggling with 2criteria lookups in Excel! This guide has my top 2 solutions, and advice on which formula to use  INDEX and MATCH, or FILTER. You can watch my stepbystep videos, or follow the written steps, and download the sample files for an Excel lookup with 2 criteria. Author: Debra Dalgleish 
Excel Lookup With 2 CriteriaThere 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:
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 the Excel INDEX and MATCH functions. 
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. INDEX/MATCH Functions
FILTER Function
Other SolutionsAlso, there are other ways to do a Microsoft Excel lookup with multiple criteria. Take a look at the examples on the following pages, with Excel LOOKUP and VLOOKUP function examples:  Excel LOOKUP function for Multiple Criteria  Excel VLOOKUP formula for 2column lookup Also, see more information about Excel's different lookup functions. 
Videos: Excel Lookup With 2 CriteriaTo 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, 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 MATCHTo 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 MATCHBefore using INDEX and MATCH with multiple criteria, let's see how they work together in a simple formula. In the below 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.
=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0)) The formula returns the correct price for the sweater  10. 
How Simple INDEX MATCH Formula WorksHere’s how that simple INDEX / MATCH formula finds the correct price for sweater.
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) MATCHThe MATCH function has 3 arguments in its syntax:
In this example, the MATCH function looks for the value from cell A7, in the range B2:B4, with an exact match type.
2) INDEXThe INDEX function has 3 arguments:
In this example, the INDEX function returns 10  the price from row 1, in the range C2:C4 
INDEX/MATCH 2 CriteriaIn the previous example, the match was based on one criterion  the Item name. For the next criteria 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. Note: The numbers in column A are for information only  they are not used in the formulas
INDEX/MATCH Formula 2 CriteriaTo calculate the price based on 2 criteria, enter this arrayentered* 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. An array function is not required
*Arrayentered  Press Ctrl + Shift + Enter, instead of just pressing the Enter key. That will automatically add curly brackets around the array formula. 
How the 2 Criteria Formula WorksHere's how this INDEX MATCH multiple criteria formula works. INDEX FunctionProduct prices are in cells E2:E10, and INDEX will return a Price from that range.
MATCH Function  Find the RowThe 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
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. 
INDEX/MATCH ResultSo, 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. FILTER FunctionIf 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. Criteria CellsAt the top of the sheet, two criteria have been entered, for the product specifications
Based on those criteria, we need two results  the product's Price and the product's alphanumeric Code. 
Get Product Price with FILTER FunctionFirst, in cell C2, we'll enter a dynamic array FILTER formula to calculate the product price, based on the criteria in cells A2 and B2. The FILTER function has 3 arguments:
1) Array ArgumentProduct prices are stored in this range:
So, to start the formula,
Excel will automatically enter a reference to the Price column, in a structured table reference. The formula should look like this:

2) Include ArgumentFor 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)) Product Code with FILTERThe 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)) 
Get the Sample File

More TutorialsINDEX / MATCH Functions  Use the INDEX and MATCH functions to pull data from a list. These functions can be used together, to create a powerful and flexible formula. Also, tips for troubleshooting the MATCH function Compare Lookup Functions  How to pick the best lookup function in Excel. Compare VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, and OFFSET VLOOKUP Function  Use VLOOKUP to find product price, or student grades. See how to find and fix VLOOKUP formula problems with troubleshooting 
Last updated: April 13, 2024 11:53 AM