Excel INDEX MATCH with multiple criteria - formula examples

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:

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

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.

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:
Excel INDEX MATCH formula with multiple criteria

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:
How INDEX MATCH with multiple criteria works

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:

INDEX(return_range, MATCH(1, INDEX((criteria1=range1) * (criteria2=range2) * (..), 0, 1), 0))

For our sample dataset, the formula goes as follows:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Non-array INDEX MATCH formula with multiple criteria

How this formula works

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:

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}

This array is passed to the MATCH function:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

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, 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:

{=INDEX(table_array, MATCH(vlookup_value, lookup_column, 0), MATCH(hlookup_value1 & hlookup_value2, lookup_row1 & lookup_row2, 0))}

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:
INDEX MATCH with multiple criteria in rows and columns

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!

Available downloads

Please feel free to download Excel INDEX MATCH multiple criteria sample workbook with all the formulas discussed in this tutorial.

Other ways to look up with several conditions in Excel

VLOOKUP with multiple criteria – this requires adding a helper column to the left of your table and concatenating all criteria in that column. That's not a very elegant solution, but you may want to know all available options.

XLOOKUP with multiple criteria – this recent addition to the family of Excel's lookup and reference functions (currently available only to Office 365 subscribers) handles arrays by design. Meaning, it works as a regular formula, not an array formula!

Lookup multiple criteria and return multiple results - combine 5 different functions in a single formula to evaluate several criteria and return all matches either in a column or row.

Merge Tables Wizard - visual way to Vlookup in Excel with one or more criteria.

38 Responses to "Excel INDEX MATCH with multiple criteria - formula examples"

  1. kanth reddy says:

    why should not try like this type
    INDEX(E3:E14,MATCH(TRUE,COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4),0))
    INDEX(E3:E14,MATCH(1,(--(COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4))),0))

    • Hi Kanth,

      Thank you for your feedback.

      These formulas won't work. The point is that COUNTIFS returns the count of cells for which all the criteria are TRUE, while the MATCH function needs a lookup array (not a count!) in which it can find the row(s) that meet all the criteria and pass the relative position of the first found row to INDEX.

  2. Pat W says:

    I have a database with detail of receipts and expenses for a number of different customers. Is there a way for me to pull data for a particular customer in date order. I could just filter by Customer but I am trying to create a subsidiary account by customer which would be done in a different sheet.

    1/1/19 Fred Jones December Charges 800 dr
    1/30/19 Fred Jones January Charges 800 dr
    1/30/19 Fred Jones Payment 300 cr

  3. Sam says:

    Thank you!

  4. Muhammad Memon says:

    This is an excellent explanation. Thank you so much!!

  5. Bill says:

    I'm trying to lookup a value in a table where the row and column values are a range of numbers. Then I need the result to be placed in an Excel spreadsheet where the criteria is located and varies from each location. Here's a sample table:
    Gr Tgt 6
    2-3 1 3 4 6 7
    4 2 4 5 7 8
    5-6 3 5 6 8 9
    7-8 4 6 7 9 10
    9-10 5 7 8 10 10
    So, by specifying a value in the 1st row and a number in the 1st column I need to capture the intersection. Thnaks

    • Hello Bill!
      If I understand your problem right, you want to specify values from a row and a column to pull a number at the intersection of that row and that column. In this case, you may try the following formula:

      =INDEX(A1:F5, MATCH("4",A1:A5,0), MATCH(4,A1:F1,0))

      Here "4" is the text we search for in the first column, 4 is a number we search for in the first row. The INDEX function will return a value that is located at the intersection of the row and column where the MATCH function finds "4" and 4.
      I hope it will solve your problem. If it is not the result you’d like to get, please describe your task in more detail so that I will be able to understand it and help you better.

  6. ROHAN says:

    1)If= first month(1 sep 2019 to 2 feb 2020) all floors commission 2%
    2)If= second month (3 feb 2020 to 29 Feb 2020) floor wise commission
    Lower floor - 3%
    Middle floor - 3.5%
    Higher floor - 4 %
    3) if = third month ( 1 March 2020 to 31 march 2021) onwards floor wise commission
    Lower floor - 2 %
    Middle floor - 2.5%
    Higher floor -3%
    AND
    3 TYPES OF SOURCE(X,Y,Z)
    Y SOURCE ELEGIBLE ONLY 2% ALL TYPES OF CONDTION

    CAN YOU PLEASE HELP WHICH FORMULA WORKING IN 1 CELL excel...

  7. Man says:

    I have a macro-enabled Excel template. I'm trying to figure out which formula(s) I can include my template in order to achieve the following with a single click of a button:

    ▪ Search for a column name that exists in a different spreadsheet (same workbook)
    ▪ Identify the starting and ending cells of the column
    ▪ Store the start and end cells as a variable
    ▪. Use the variable as an input array for a SUMPRODUCT() calculation

    The column to be identified will change in size, since I will be working with multiple imported data sets, and they are all different. Can this be done without VBA?

  8. Lynn says:

    Hi,
    How do I put in the formula if one of the criteria is in a range of numbers e.g. 20 - 29?
    E.g. 1st criteria is age, 2nd criteria is exercise time: 10-15 mins,
    so results is if age 20 exercises 12 mins = normal
    if age 20 exercises 9 mins = weak
    exercises 18 mins = strong, etc

    • Hello Lynn!
      Please use the example above: "INDEX MATCH with several criteria - formula example".
      Please enter age in column A, exercise time – in column B, and estimation – in column D.
      Fill in the table with all possible age variants and exercise time with the corresponding estimation.
      After that, you will search for a necessary row in this table using the recommended formula.

      If you still have any questions, I will be happy to help you further.

  9. John says:

    Hello,

    I have a chart with many names which are duplicates in A and the status of the file (Complete or Incomplete) in C. I wanted the names that showed up more than once which had Incomplete on one day and Complete on the other. So I did that and by using this formula: =IF(AND(COUNTIFS(A:A,A2,C:C,"complete"),COUNTIFS(A:A,A2,C:C,"incomplete")),A2,"")

    Because the above formula needed to be dragged down and repeats the names I wanted, I wanted another list that shows me the names generated by the above formula once in a nice neat order - one after the other. Someone gave me this array formula which worked perfectly: =IFERROR(INDEX($F$2:$F$21,MATCH(0,COUNTIF($G$1:$G1,$F$2:$F$21),0)),"")

    What I am hoping to get help with is, that countif shows me {1;1;0;1;1;0;1;0;0;1;1;1;0;1;1;0;1;0;0;1} and every 0 is the name that I want generated by the previous formula. The 0 in lookup_value matches that name and the 0 exact match gives it to me. What I don't understand after reading above is, does 0 mean false or true? In the above all the true were 1 and false 0. Why is 0 false when 0 gives me the name I want? Unless countif 0=true and 1=false. Also, I don't understand why the range is in G1 - the signiface.

    This formula starts at F2: =IF(AND(COUNTIFS(A:A,A2,C:C,"complete"),COUNTIFS(A:A,A2,C:C,"incomplete")),A2,"")

    This starts at G2: =IFERROR(INDEX($F$2:$F$21,MATCH(0,COUNTIF($G$1:$G2,$F$2:$F$21),0)),"")

    If anyone can explain that would be great as i am confused and evaluating formula all the time only helped me to a certain extent.

    Thanks!

  10. JeongHwan Kim says:

    Hello?
    I am using the following formula to see what numbers (fill color code) are in column N.
    =INDEX($N$1:$N$1200,MATCH(N(TRUE),INDEX(($N$1:$N$12000)*($N$1:$N$12006)*($N$1:$N$120044),0,1),0))
    I just want to change the number of the criteria in the inner INDEX function depending on the number of the found numbers, e.g. 3 in the above formula. It can be 3, 4, ...
    .
    Any way to solve the problem?
    Many thanks in advance.

  11. JeongHwan Kim says:

    In the comment just sent, the NOT EQUAL sign "" is deleted during the posting. I do not know why though. It should be
    $N$1:$N$12000, $N$1:$N$12006, $N$1:$N$120044.

  12. Enan says:

    Hi and thank you for such a wonderful post.
    I am unable to understand how the formula has returned 3 from below expression.
    {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}
    Appreciate your help.

    • Hello Enan!
      If you perform mathematical operations with the logical values TRUE and FALSE, then Excel turns them into numbers 1 and 0.
      I hope I answered your question.

      • Enan says:

        Dear Alexander, Thank you for your response.
        i am unable to understand. I want to know how this 3 comes as shown below.
        =INDEX(D2:D13, 3)
        What i understood is:
        ={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}
        ={1}*{1}*{1}
        =1
        Then how come it is coming 3.
        Appreciate your help.

  13. Tom says:

    Great article - thanks.
    In your experience, which is the fastest/most efficient approach when there is a lot of data?

  14. Stephanie says:

    This was an amazing explanation! I was stuck for a day trying to figure out how to return a cell value (year) based on the max value of a City's population. This did the trick for me! I used a nested: =IFNA(INDEX([year column], MATCH(1,(MAXIFS(..)= [population column])*(@city_name = [Cities Column]),0)),"NOT FOUND"). Thank you so much for taking the time to write this. You taught me something new!!

  15. Anggit says:

    Hello,
    If i want to allocated date by month and with multiple criteria, should i use index match only or add "if" formula?

  16. jamuna nadarajah says:

    Hi There,
    I am trying to create a dynamic income statement in excel, i have attached the link for my spread sheet here

    in task 2 i have a list of columns with calculations, in task 3 in the revenue column i wrote =INDEX(Total_ revenue,MATCH(1,(Company_name=C6)*(years=D11),0)) , i thnik the problem is that company_name is a name field with data validation and i need to write another formula connecting it to the revenue column, any ideas , i would vey much appreciate your help
    Thank you very much
    Jamuna

  17. P M Murali says:

    how can we make an index formula for
    in one column I want a specific text from it
    in the second column also I want a different specific text
    from the third column it is numbers and we want to put a formula of largest number
    fourth column in index column
    A b c d
    apple delhi 15 abc Ltd
    benana chenni 20 x ltd
    grape delhi 25 abc ltd
    apple mumbai 40 y ltd
    benana pune 30 x Ltd

    example index delhi, with specification of "apple" "abc Ltd" with smallest number a sper column c

    please reply sir

  18. RobertH says:

    I have been stuck trying to get cell content based on row and column matches. I have one worksheet that contains base data of 3 columns (A = Dates, B = Facilities, C = Customers). On worksheet 2 we want to display the information in a linear calendar style that includes each day of a year. The dates are across the top row starting at Column B. Column A is a list of facilities. We want to put the customer into the appropriate cell within the correct facility row and under the correct date(s) column(s). I have tried using pivot tables but I just end up with a count of customers and not the actual customer name in the given cell.
    In most cases there is only ever just one customer per facility per day. But on occasion one may just have part of a day and another may have the evening portion. In those cases just listing both with a separator ? or a - would serve our purpose.

    I am sure there is a way to do this but looking all over the place and asking have not yielded an answer.

    • Hello!
      On Sheet2 in cell B2, write down the formula.
      =CONCAT(IFERROR(INDEX(Sheet1!$C$2:$C$20, SMALL(IF(Sheet2!B$1&Sheet2!$A2=Sheet1!$A$2:$A$20&Sheet1!$B$2:$B$20, ROW(Sheet1!$C$2:$C$20)-1), ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$20)))))&"-",""))
      Then copy it down the column.
      Sheet1!$C$2:$C$20 - Customers
      Sheet1!$A$2:$A$20 - Dates
      Sheet1!$B$2:$B$20 - Facilities
      Sheet2!B$1 - Date
      Sheet1!$A$2:$A$20 - Facilities

      If there is anything else I can help you with, please let me know.

      • RobertH says:

        Alexander thanks for the help. When I plug in the formula, I get a #NAME? error and the highlighted in red and blue part of the formula is this part Sheet2!B$1&Sheet2!$A2

  19. RobertH says:

    I have to apologize. It works just fine with Office 365, but when I try on the work computer which is running Office 2013 I get the error.
    I assume the below info are what the references are, correct?

    • Hello!
      Check the sheet names in your workbook and correct the links

      • RobertH says:

        Found one.
        In some cases, a customer conducts multiple events at a given facility on the same day. We want to just list each customer once so want to remove duplicates if possible. Not sure if I would have to split out the AllData sheet into separate sheets/tables to make it easier.
        I really appreciate all the help Alexander.

  20. Phisaw says:

    Hello! Thanks for the great article. Can the Index/Match formula contain a cell value that also contains an index/match formula? I have the following: S2 contains =Index(Rep_ID, Match(A2, OrderNumber,0)) - (rep_ID and OrderNumber are from AcctsList sheet);
    T2 contains =Index(SalesRepName, Match(S2,SalesRepID, 0))- (SalesRepName and SalesRepID are from Slsp Sheet).
    T2 returns #N/A unless I change S2 to the value of the formula and I don't want to have to create another column to paste values. Is there a way to combine the formulas in T2 so that it will provide the RepName needed?
    Extensive search and forum request has yielded no answers.
    Any help is greatly appreciated!
    Thank you,
    Phisaw

  21. Julio says:

    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))

  22. Thankful says:

    Thank you for this article- you helped me solve my problem!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard