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.
Author: Debra Dalgleish
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:
- INDEX/MATCH functions (all Excel versions)
- 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 the Excel INDEX and MATCH functions.
Excel Lookup With 2 Criteria
Use FILTER or INDEX/MATCH ?
Videos: Excel Lookup with 2 Criteria
INDEX and MATCH
--Simple INDEX MATCH
--How Simple INDEX MATCH Formula Works
INDEX/MATCH 2 Criteria
--MATCH True or False
--MATCH Both True
--Lookup With Multiple Criteria
--How the Complex Formula Works
FILTER Function
Get the Sample File
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
- 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, with Excel LOOKUP and VLOOKUP function examples:
-- 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.
- 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.
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 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.
- 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.
How Simple INDEX MATCH Formula Works
Here’s how that simple INDEX / MATCH formula finds the correct price for sweater.
- First, the MATCH function finds the location of "Sweater" in cells B2:B4, and returns that number
- 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 in its syntax:
- 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 type.
- 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 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.
- 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.
Note: The numbers in column A are for information only - they are not used in the formulas
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. An array function is not required
- =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.
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
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.
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
Based on that result, INDEX returns the price for the 8th product in the price lookup table.
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.
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.
Get Product Price with FILTER Function
First, 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:
- array
- include
- if_empty (optional)
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))
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))
Get the Sample File
- INDEX/MATCH: To follow along with the INDEX/MATCH video, download the zipped sample Lookup with Multiple Criteria file. The workbook is in xlsx format, and does not contain any macros.
- FILTER Function Examples: To see the four FILTER examples, download the FILTER function examples workbook. The file is zipped, and is in Excel xlsx format, with no macros.
More Tutorials
INDEX / 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
INDEX / MATCH Functions
FILTER function examples
Last updated: July 10, 2022 3:05 PM
FAQs
Can you use INDEX match with multiple criteria? ›
INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches. In other words, you can look up and return values even if there are no unique values to look for.
Should I use VLOOKUP or INDEX match? ›VLOOKUP will only work if the lookup value is in the first column. VLOOKUP cannot look to its left. However, INDEX MATCH solves this problem as it performs the lookup both horizontally and vertically. So, it doesn't require the lookup value to be in the first column, it can be anywhere.
Is Xlookup more efficient than INDEX match? ›Because calculation times for VLOOKUP and INDEX/MATCH are on a similar level, the performance of XLOOKUP compared to INDEX/MATCH doesn't surprise much: XLOOKUP is significantly slower than INDEX/MATCH as well.
Can you do a VLOOKUP with 2 criteria? ›By default, 'VLOOKUP' cannot help with a two-criteria (or more) lookup. It is customary to change to the more flexible combination of functions, 'INDEX' and 'MATCH' and turning the formula into an array formula. However, this is not as easy and intuitive as using a simple 'VLOOKUP'.
How do I do a VLOOKUP with multiple lookups? ›- Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
- Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.
VLOOKUP can only be used for looking up values from Left to Right. INDEX MATCH can look up the values from Left to Right as well as Right to Left. VLOOKUP only can lookup through vertical lines, i.e. columns and not through rows. INDEX MATCH can lookup values through rows as well as columns.
How is Xlookup better than VLOOKUP? ›XLOOKUP is much more flexible than VLOOKUP, which can look up values only in the leftmost column of a table, and return values from corresponding columns on the right, as we saw in the example above. In contrast, the XLOOKUP model requires simpler steps and can return values for any column in either direction.
Is INDEX match slower than VLOOKUP? ›INDEX MATCH formula
With both tables on the same sheet, INDEX MATCH performed much slower than VLOOKUP (8.9 against 6.6 seconds). But if we move the lookup table to another worksheet, the formula starts working much faster (~ 5 seconds), which is better than VLOOKUP.
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
Which is faster VLOOKUP or INDEX match or Xlookup? ›Let's recap how XLOOKUP outperforms VLOOKUP and INDEX/MATCH: It is the simplest function, with only 3 arguments needed in most cases because the default match_mode is 0 (exact match). It's a single function, unlike INDEX/MATCH, so it's faster to type.
Is there any reason to use VLOOKUP over Xlookup? ›
VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.
What can I use instead of Xlookup? ›- METHOD 1: USING INDEX/MATCH.
- METHOD 2: USING VLOOKUP.
- METHOD 3: USING QUERY.
- METHOD 4: USING FILTER.
Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any additional matching rows. Once VLOOKUP finds a matching row, it shoots to the right to retrieve the related value from a single column.
Which of the following are main reasons to use INDEX match instead of VLOOKUP? ›INDEX and MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right, which is all VLOOKUP can do.
Can Xlookup do INDEX match match? ›Two-Way Nested XLOOKUP
In this lesson, you will learn about how XLOOKUP can be used to replace INDEX MATCH when you need Excel to take two user defined inputs.
The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.
Does Xlookup retrieve all matching values? ›Can You Return All Matches With XLOOKUP? Currently, you cannot return multiple lookup results with Excel's XLOOKUP function. You can learn exactly what XLOOKUP can and can't do in my XLOOKUP function overview guide.
What is the best lookup function in Excel? ›The most commonly used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.
What is the 4th argument in VLOOKUP? ›The VLOOKUP function also has an optional fourth argument: range lookup. This can be either TRUE or FALSE. If the range lookup argument is FALSE, VLOOKUP will find only exact matches. If the range lookup argument is TRUE, or if a range lookup argument is not entered, VLOOKUP can find approximate matches.
What are the 4 parameters in VLOOKUP? ›VLOOKUP takes four arguments: lookup_value, table_array, column_index_num, and range_lookup. Lookup_value is the value to look for, and table_array is the range of vertical data to look inside. The first column of table_array must contain the lookup values to search.
How do you filter multiple criteria? ›
- Insert at least three blank rows above the list range that can be used as a criteria range. ...
- To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. ...
- Click a cell in the list range.
Dynamic Arrays are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell. Through over 30 years of history, Microsoft Excel has undergone many changes, but one thing remained constant - one formula, one cell.
How do you filter in Excel with multiple values from a list? ›- Select the cell of interest and click Apply Filter by Selected Value.
- Filter by selected value is created.
- Select several cells and click Apply Filter by Selected Value.
- The list is filtered by multiple values.
- Clear all filters in one click.
- Select the entire data set (including the headers).
- Go Data tab –> Sort & Filter –> Advanced. (You can also use the keyboard shortcut – Alt + A + Q). ...
- In the Advanced Filter dialog box, use the following details: Action: Select the 'Copy to another location' option. ...
- Click OK.
VLOOKUP is an obsolete function inherited from Lotus-123. There is much better in Excel, more powerful and less limited, it is INDEX/MATCH. INDEX/MATCH replaces all lookups functions (VLOOKUP, HLOOKUP and LOOKUP).
Why did Xlookup stop working? ›error in your XLOOKUP function, the most likely reason is that your lookup array and your return array are not the same size. In my example, the reason the arrays are different sizes is that there is a blank cell at the end of my return array.
How many criteria can INDEX match have? ›Although Microsoft Excel provides special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows.
Can you use INDEX match to sum multiple columns? ›There are three easy ways to sum multiple cells with the INDEX match function: SUM family function. AGGREGATE function. SUBTOTAL function.
Can INDEX match lookup in multiple columns? ›Multiple columns.
Unlike VLOOKUP, INDEX-MATCH can index multiple columns for fillable output. In other words, the array can be multiple columns.
Microsoft recently announced the upcoming release of a new function called XLOOKUP. This function will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.
Which is faster INDEX match or Sumifs? ›
From a purely speed perspective LOOKUP and INDEX-MATCH (type 1) are the fastest, followed by INDEX-MATCH (type 0), with SUMIFS the slowest as it is required to scan the entire criteria range whilst the other functions stop once they find a match.
How do I use multiple column INDEX in Vlookup? ›The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.
Is Sumproduct faster than INDEX match? ›Active Member. VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT at looking up an item in a list. SUMPRODUCT is used for crunching lots of numbers together based on conditions. Yes, it conceivably be used in place of VLOOKUP or INDEX/MATCH.
Can we use Xlookup for multiple lookup values? ›One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!).
Can you use VLOOKUP and INDEX match together? ›VLOOKUP is a single formula that does all the lookup-and-fetch, but with INDEX/MATCH, you need to use both the functions in the formula. INDEX/MATCH can more advanced lookup – such as lookup to the left of the dataset, make row/column value dynamic, etc.
Is Xlookup better than VLOOKUP? ›XLOOKUP is much more flexible than VLOOKUP, which can look up values only in the leftmost column of a table, and return values from corresponding columns on the right, as we saw in the example above. In contrast, the XLOOKUP model requires simpler steps and can return values for any column in either direction.
What is the difference between a VLOOKUP and Xlookup? ›The XLOOKUP defaults to an exact match where the VLOOKUP defaults to an approximate match. As the exact match is used most often, this setting would make the XLOOKUP more effective. On top of this, the XLOOKUP offers an additional option of an approximate match returning the next larger value.