by The tutorial shows how to lookup with multiple criteria in Excel using INDEX and MATCH and a few other ways.
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. This tutorial explains the syntax and inner mechanics in full detail so that you can easily adjust the formula for your particular needs. To make the examples easier to follow, you are welcome to download our sample workbook.
Excel INDEX MATCH with multiple criteria
When working with large databases, you may sometimes find yourself in a situation when you need to find something but don't have a unique identifier for the search. In this case, lookup with several conditions is the only solution.
To look up a value based on multiple criteria in separate columns, use this generic formula:
Where:
- Return_range is the range from which to return a value.
- Criteria1, criteria2, … are the conditions to be met.
- Range1, range2, … are the ranges on which the corresponding criteria should be tested.
Important note! This is an array formula and it must be completed with Ctrl + Shift + Enter. This will enclose your formula in {curly brackets}, which is a visual sign of an array formula in Excel. Do not try typing the braces manually, that won't work!
The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. To evaluate multiple criteria, we use the multiplication operation that works as the AND operator in array formulas. Below, you will find a real-life example and the detailed explanation of the logic.
Tip. In Excel 365 and 2021, you can use XLOOKUP formula with multiple criteria.
INDEX MATCH with several criteria - formula example
For this example, we will be using a table in the so-called "flat-file" format with each separate criteria combination (region-month-item in our case) on its own row. Our goal is to retrieve the sales figure for a certain item in a specific region and month.
With the source data and criteria in the following cells:
- Return_range (sales) - D2:D13
- Criteria1 (target region) - G1
- Criteria2 (target month) - G2
- Criteria3 (target item) - G3
- Range1 (regions) - A2:A13
- Range2 (months) - B2:B13
- Range3 (items) - C2:C13
The formula takes the following shape:
=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))
Enter the formula, say in G4, complete it by pressing Ctrl + Shift + Enter and you will get the following result:
How this formula works
The trickiest part is the MATCH function, so let's figure it out first:
MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))
As you may remember, MATCH(lookup_value, lookup_array, [match_type]) searches for the lookup value in the lookup array and returns the relative position of that value in the array.
In our formula, the arguments are as follows:
- Lookup_value: 1
- Lookup_array: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
- Match_type: 0
The 1st argument is crystal clear - the function searches for the number 1. The 3rd argument set to 0 means an "exact match", i.e. the formula returns the first found value that is exactly equal to the lookup value.
The question is - why do we search for "1"? To get the answer, let's have a closer look at the lookup array where we compare each criterion against the corresponding range: the target region in G1 against all regions (A2:A13), the target month in G2 against all months (B2:B13) and the target item in G3 against all items (C2:C13). An intermediate result is 3 arrays of TRUE and FALSE where TRUE represents values that meet the tested condition. To visualize this, you can select the individual expressions in the formula and press the F9 key to see what each expression evaluates to:
The multiplication operation transforms the TRUE and FALSE values into 1's and 0's, respectively:
{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}
And because multiplying by 0 always gives 0, the resulting array has 1's only in the rows that meet all the criteria:
{0;0;1;0;0;0;0;0;0;0;0;0}
The above array goes to the lookup_array argument of MATCH. With lookup_value of 1, the function returns the relative position of the row for which all the criteria are TRUE (row 3 in our case). If there are several 1's in the array, the position of the first one is returned.
The number returned by MATCH goes directly to the row_num argument of the INDEX(array, row_num, [column_num]) function:
=INDEX(D2:D13, 3)
And it yields a result of $115, which is the 3rd value in the D2:D13 array.
Non-array INDEX MATCH formula with multiple criteria
The array formula discussed in the previous example works nice for experienced users. But if you are building a formula for someone else and that someone does not know array functions, they may inadvertently break it. For example, a user may click your formula to examine it, and then press Enter instead of Ctrl + Shift + Enter. In such cases, it would be wise to avoid arrays and use a regular formula that is more bulletproof:
For our sample dataset, the formula goes as follows:
As the INDEX function can process arrays natively, we add another INDEX to handle the array of 1's and 0's that is created by multiplying two or more TRUE/FALSE arrays. The second INDEX is configured with 0 row_num argument for the formula to return the entire column array rather than a single value. Since it's a one-column array anyway, we can safely supply 1 for column_num: This array is passed to the MATCH function: MATCH finds the row number for which all the criteria are TRUE (more precisely, the the relative position of that row in the specified array) and passes that number to the row_num argument of the first INDEX:=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))
How this formula works
INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)=INDEX(D2:D13, 3)
INDEX MATCH with multiple criteria in rows and columns
This example shows how to perform lookup by testing two or more criteria in rows and columns. In fact, it's a more complex case of the so-called "matrix lookup" or "two-way lookup" with more than one header row.
Here's the generic INDEX MATCH formula with multiple criteria in rows and columns:
Where:
Table_array - the map or area to search within, i.e. all data values excluding column and rows headers.
Vlookup_value - the value you are looking for vertically in a column.
Lookup_column - the column range to search in, usually the row headers.
Hlookup_value1, hlookup_value2, … - the values you are looking for horizontally in rows.
Lookup_row1, lookup_row2, … - the row ranges to search in, usually the column headers.
Important note! For the formula to work correctly, it must be entered as an array formula with Ctrl + Shift + Enter.
It is a variation of the classic two-way lookup formula that searches for a value at the intersection of a certain row and column. The difference is that you concatenate several hlookup values and ranges to evaluate multiple column headers. To better understand the logic, please consider the following example.
Matrix lookup with multiple criteria - formula example
In the sample table below, we'll be searching for a value based on the row headers (Items) and 2 column headers (Regions and Vendors). To make the formula easier to build, let's first define all the criteria and ranges:
- Table_array - B3:E4
- Vlookup_value (target item) - H1
- Lookup_column (Row headers: items) - A3:A4
- Hlookup_value1 (target region) - H2
- Hlookup_value2 (target vendor) - H3
- Lookup_row1 (Column headers 1: regions) - B1:E1
- Lookup_row2 (Column headers 2: vendors) - B2:E2
And now, supply the arguments into the generic formula explained above, and you will get this result:
=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))
Remember to complete the formula by pressing the Ctrl + Shift + Enter shortcut, and your matrix lookup with multiple criteria will be done successfully:
How this formula works
As we are searching vertically and horizontally, we need to supply both the row and column numbers for the INDEX(array, row_num, column_num) function.
Row_num is delivered by MATCH(H1, A3:A5, 0) that compares the target item (Apples) in H1 against the row headers in A3:A5. This gives a result of 1 because "Apples" is the 1st item in the specified range.
Column_num is worked out by concatenating 2 lookup values and 2 lookup arrays: MATCH(H2&H3, B1:E1&B2:E2, 0))
The key factor for success is that the lookup values should match the column headers exactly and be concatenated in the same order. To visualize this, select the first two arguments in the MATCH formula, press F9, and you will see what each argument evaluates to:
MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)
As "NorthVendor 2" is the second element in the array, the function returns 2.
At this point, our lengthy two-dimensional INDEX MATCH formula transforms into this simple one:
=INDEX(B3:E5, 1, 2)
And returns a value at the intersection of the 1st row and 2nd column in the range B3:E5, which is the value in the cell C3.
That's how to look up multiple criteria in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel INDEX MATCH multiple criteria (.xlsx file)
291 comments
Hello Alexander,
This formula is for index match for multiple criteria has really helped me a lot. Thank you! My question is, if all the criteria has been fulfilled but I want the value below that cell to be reflected, how would the formula look like?
This is a sample formula that I use, when there's just one criteria:
=INDEX(Sheet39!B:B,MATCH(A3,Sheet39!B:B,0)+2,1)
And here's an example of the formula with multiple criteria that I would like to reference a cell below:
=IFERROR(INDEX(Sheet39!B:B,MATCH(1,(Sheet39!D:D=B$3)*(Sheet39!A:A=$A$1),0)),"")
Thank you in advance!
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice.
Your formula should follow the guidelines in this section.
Hello Alexander,
Apologies for not being clearer, please see sample below:
NAME AGE PLACE
ADAM 10 LA
BRODY 11 LA
CHARLIE 12 LA
ADAM 8 CA
LUKE 9 CA
CHARLES. 10 CA
if I want to see the result for who is the next to ADAM in CA for example (as there are many Adams in different places), but it's important to see the one who is listed next to him (cell below), I am trying to use =INDEX(A:A,MATCH(A4,A:A,0)+1,1) where i typed ADAM on A4. The result here is BRODY.
So if I want "LUKE" to show, I will then have to create multiple criterias for Adam, to include, say the AGE or PLACE. And unfortunately I am unable to do it with this formula for multiple criterias: =IFERROR(INDEX(A:A,MATCH(1,(A:A=A4)*(C:C=CA),0)),""). I can't seem to place the +1 or +2 cell below to show the result of LUKE or CHARLES.
Thank you in advance for your assistance!
Hello!
If I got you right, the formula below will help you with your task:
=IFERROR(INDEX(A:A,MATCH(1,(A:A=A4) * (C:C="CA"),0)+1),"")
I hope I answered your question. If something is still unclear, please feel free to ask.
Thank you Alexander for the assistance. Really appreciate it. Have a good day and keep safe!
Hello,
I have a list of products and it has multiple date. I required all the date against the product one by one.
when i used the below formula i got the smallest date (1/31/2019) in the list against one of the product. and still there are more 11 dates i should get. but when i copied this formula in the next column its showing an error.
=INDEX('Evaluation data'!$W$2:$W$12277, SMALL(IF($A2='Evaluation data'!$M$2:$M$12277, ROW('Evaluation data'!$M$2:$M$12277)-ROW('Evaluation data'!$M$2)+1), COLUMN(A:A)))
Colum M Colum W
TYRE 1 1/31/2019
TYRE 1 1/31/2019
TYRE 1 4/10/2019
TYRE 1 4/10/2019
TYRE 1 5/3/2019
TYRE 1 5/3/2019
TYRE 1 5/3/2019
TYRE 1 5/3/2019
TYRE 1 6/11/2019
TYRE 1 6/11/2019
TYRE 1 7/10/2019
TYRE 1 8/15/2019
TYRE 1 9/13/2019
TYRE 1 5/25/2020
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets.
Please check out this article to learn how to vlookup multiple matches in Excel with one or more criteria.
I hope my advice will help you solve your task.
hi, thank you for the great explanations!!
hopefully you could solve my problem:
i have a table of different plants varieties (column a) and the flowering date (column b) for each variety, i have another table with the height of the plant varieties measured daily (the first column is the list of the varieties and each date of measuring the height is a separate column). i would like to find the height of each variety at its flowering date. i.e find the match between the flowering date and return the height measured at this date
thanks
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
If you cannot solve the problem yourself, please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com. Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
I wonder if it's possible to use the criteria from one sheet to search for information on a second chart.
I want to see if the annual salary for each job title falls in the min, mid, or max range for each job level.
Every I try gets errors.
Chart 1
JOB TITLE JOB LEVEL ANNUAL SALARY MARKET-RATIO
Analytics Developer Junior $60,007.00
Analytics Developer Lead $95,009.00
Automation Tester Senior $95,009.00
Business Analyst Intermediate $95,009.00
Content Writer Senior $64,018.00
Visual Designer Senior $110,016.00
Visual Designer Senior $95,009.00
Chart 2
Job Title Level Min Mid Max
Analytics Developer Junior 60,000 75,000 90,000
Intermediate 70,000 87,000 104,000
Senior 82,000 103,000 124,000
Automation Tester Junior 56,000 70,000 84,000
Intermediate 70,000 87,000 104,000
Senior 79,000 99,000 119,000
Business Analyst Junior 56,000 70,000 84,000
Intermediate 68,000 85,000 102,000
Senior 85,000 106,000 127,000
Content Writer Junior 54,000 68,000 82,000
Intermediate 64,000 80,000 96,000
Senior 80,000 100,000 120,000
Visual Designer Junior 56,000 70,000 84,000
Intermediate 68,000 85,000 102,000
Senior 85,000 106,000 127,000
Hello!
Please specify, what formula you used and what problem or error occurred.
It’ll help me understand it better and find a solution for you.
Hi,
I have data like this:
Col B Col C Col D Col E
1 Project 12-Oct 19-Oct 26-Oct
2 12345 START MIDDLE FINISH
3 78989 MIDDLE FINISH TEST
In B15 I want to have the current status of project to show based on the current date (B14 has the current Monday).
How can I use index match for that?
Thanks,
What does 1-2-3 mean ? Is it the value of one cell or several ?....
sir ji 1-2-3 means its value of several cell....which is in one cell and i need only sum of its corresponding value ...
There are 2 sheets in which one column (alpha numeric value) is same in both the cases. The two sheets to be compared and give the value in column if both the cases are matching. The other column data to be called.
Please help.
Hello!
We have a tool that can solve your task in a couple of clicks: Ablebits Data - Compare Sheets.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I am looking to do something that is a bit of an extension to this. I have a round-robin league set up with 5 columns -- a match number and four player name columns. Any player name could be in any of the four columns for any given match. I want to be able to find the match number (or numbers) when one to four players are entered as search criteria. I think the Index-Match approach will work but I need to return an array of match numbers (which may have one or more values) and the matching has to have logic to exclude blank search criteria fields.
Match# Player_1 Player_2 Player_3 Player_4
1 Tom Tim Steve John
2 Mary Alan Jim Tom
3 Alan John Mark Ed
4 Jim Mary Tim Alan
Search_1:
Search_2:
Search_3:
Search_4:
Return Array:
Match #s
Example 1:
Search_1: Alan
Return Array:
2
3
4
Example 2:
Search_1: John
Search_2: Alan
Return Array:
3
Example 3:
Search_1: Mary
Search_2: Alan
Search_3: Jim
Return Array:
2
4
Thanks for any insight on an approach.
Hi, i need some help using the below template formula:
=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))
it only returns one data instead of listing the rows with the same criterias met
Below is my data example:
*Sheet 1*(Raw data)-
Column A: List of Coach Names (Not Unique)
Column B: Student Name
Column C: Tuition Day
Column D: Tuition Time
*Sheet 2*-
Cell A1: Coach Name (e.g. Tom)
Cell A2: Tuition Day (e.g. Monday)
Cell A3: Tuition Time (e.g. 2pm)
Cell B1(Column B): Formula Output list of cells in column B that match criterias.
Criteria is- Show and list all student name if below 3 criterias are met:
1) Column A match with cell A1 (Coach Name)
2) Column C match with cell A2 (Tuition Day)
3) Column D match with cell A3 (Tuition Time)
In Sheet 2: cell A1, A2, A3, the content of the cell is changed daily manually.
I want Sheet 2: cell B1 column B to use formula to list all student names that match the criterias.
Really appreciate any assistance and insights! :)
Hello!
I recommend that you read the guide on how to find many matches by several criteria and display these matches in the table.
Hope this is what you need.
dear Sir/Mam,
i want to know if data is in 3 cell & data value is also in 3 same down side cell i want find data which is in three cell and give result sum of these data value...
data 16 18 20 22 24 26
value 240 240 240 240 240 240
data 16-18-20
sum 720
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(SUM(--(A1:C1<>""))=3,SUM(A2:C2),"")
Hope this is what you need.
NO SIR ITS NOT WORKING WHICH I LIKE THIS TYPE....
COLOUM DATA 1 2 3 16 18 20 22 24
SUM VALUE 50 500 120 240 240 240
COLOUM DATA SUM VALUE
1-2-3 670
2-3-16 860
3-18-20 600
Hello!
The formula I sent to you was created based on the description you provided in your first request. Your explanation is completely incomprehensible. What does 1-2-3 mean? Is it the value of one cell or several?
I will no longer guess what you need. In order to prevent it from happening, please provide me with the detailed description of your task.
COLOUM DATA | 1 | 2 | 3 | 16 | 18 | 20 | 22 | 24
VALUE |50| 500|120| 240|240 | 240|100|200
COLOUM DATA SUM VALUE
1-2-3 670
2-3-16 860
3-18-20 600
1-2 550
16 240
sir coloum data value which is in separate separate cell and in this cell canted only numarical value
i want when i write two or three numerical value in single cell than ans we get sum of this data value......pls help me...
What does 1-2-3 mean? Is it the value of one cell or several?....
sir ji 1-2-3 means its value of several cell....which is in one cell and i need only sum of its corresponding value ...
I'm developing a 15 x 15 matrix of results, and looking to return the column and row numbers for the MAX result in the 15 x 15 range ... any suggestions?
Hello!
If your array of values is located in the range A1: O15, then the row number with the maximum value can be found by array formula
=MIN(IF(A1:O15=MAX(A1:O15),ROW(A1:A15)))
column number -
=MIN(IF(A1:O15=MAX(A1:O15),COLUMN(A1:O1)))
This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
Thanks for the advice ... really helped me out
I'm looking for something that gives me joined text.
List is like 1) Name 2)Work Done 3)Amount 4)Month
Output list is like
1)Month Name
2)Sum of Amount for Month Given
3)Textjoin of Workdone for Month Given
Any help ??
use textjoint formula for this and textjoint formula avilable on msoffice 365 or u can use some vba codes for this also .....for more about it send me excel file on my mail...
Hi, I have a query. I have a data matrix and some of the cells are filled with Y, where the Row header and Column header combination is active. For every value Y, I need to perform a vlookup in another sheet using the corresponding row and column header values. In the example below, I need to replace the Ys with the number from 2nd sheet. Any suggestions and help is appreciated.
SHeet 1 -
AA BB CC DD
A Y
B Y
C Y
D Y
Sheet 2 -
A AA 1
B BB 2
C CC 3
D DD 4
SHeet 1 is :
AA BB CC DD
A Y
B Y
C Y
D Y
Hi
If all the information comes from one Column A & I have three criteria's by way of Race1 to Race2 & Numbers? Across 10 column with information eg:
RACE 1
A B C D E F G H I J
No Last 10 Horse Trainer Jockey Barrier Weight Penalty Hcp Rating
1
2
3
RACE 2
My question how to retrieve 1, 2, 3, & so on & all information across 10 columns?
Regards
Tony
Dear Sir,
I have to pull the districts name having 0 value in column1 and column2 both from data of 4000 rows.
Can we use Index, match formula to pull the district name. If yes pl. describe it.
If not pl. describe, how we can do this.
Pl. tell the simplest way to accomplish this task.
Regards.
Hello!
Unfortunately, without seeing your data it hard to give you advice.
Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
What If I have multiple output upon multiple criteria? Then how this formula would be?
Hello!
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Everything is competent and affordable!
Thank you!
Thank you so much u solved my 4months doubts
Thank you for this article- you helped me solve my problem!
To test it, I created the same exact table used the exact same formula (copied and pasted it) and did not work. Formula used Non-Array, got an #N/A.
=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))
Hello!
I hope you have studied the recommendations in the above tutorial. This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.