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.

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

Practice workbook for download

Excel INDEX MATCH multiple criteria (.xlsx file)

How to look up several conditions in Excel

Table of contents

291 comments

  1. Hello,

    I am trying to figure out how to utilize 3 criteria to pull a count from a table array. The fields in the table needing to be used Keyed By, Time Elapsed, and Date. Each of these fields can have multiples of the same name, time elapsed, and date. My goal is to count the amount of times the keyed by rep has exceeded this time elapsed goal on a specific day which is just going to be entered in a Date cell. I believe I need to use countifs and index match but struggling to figure out how to enter the function.

    Thanks,
    James

  2. Hi, I would like to use this index and match formula with a minimum formula as well. So i have to match on multiple criterea. I have tried to explain below, I need the return to match the name and the closest no.

    name no. name no. return
    x 5 x 4 5
    y 5 x 8 10
    x 10

    • That table got messed up

      x 5 x 4 5
      y 10 x 8 10
      x 10

  3. Need help to convert multiple row into multiple column by using index formula.

    My data is

    1 a
    1 b
    1 c
    2 a
    2 b
    2 c
    3 a
    3 b
    3 c
    3 d
    3 e
    4 a
    4 b
    4 c
    5 a
    5 b

    then the output will be.

    1 a b c d
    2 a b c
    3 a b c d e
    4 a b c
    5 a b

  4. Thanks, that was driving me nuts!

  5. I'm looking to return a 3rd value based on if two cells each meet certain criteria. For example, (in a real estate context), if anything in column A = "1 BR/1 BA" and anything in column B = 30%, return the value in column C associated with 1 BR/1 BA and 30%. The closest I've gotten so far is with the following formula:

    =INDEX(B5:B19, MATCH("1 BR/1 BA, C5:C19,0)*MATCH(30%, D5:D19,0))

    This has worked in some instances, but not all, and I'm not sure why. Thank you for any assistance you can provide!

    • Hello!
      Read the paragraph above carefully: Non-array INDEX MATCH formula with multiple criteria.
      It contains answers to your question.

  6. It is very nice to have this article.

    If I have two excel/csv files, can I make a new file to pull the data to it? (the criteria is in A file, the data is in B file)

    Thanks.

  7. Stupid web codes...

    =@INDEX(H25:N35,IF(B12 < 2.1,1,IF(AND(B12 > =2.1,B12 < 3.1),2,IF(AND(B12 > =3.1,B12 < =4),3,IF(AND(B12 > =4.1,B12 < 5.1),4,IF(AND(B12 > =5.1,B12 < 6.1),5,IF(AND(B12 > =6.1,B12 < 7.1),6,IF(AND(B12 > =7.1,B12 < 8.1),7,IF(AND(B12 > =8.1,B12 < 9.1),8,IF(AND(B12 > =9.1,B12 < 10.1),9,IF(AND(B12 > =10.1,B12 < 11.1),10,IF(AND(B12 > =11.1,B12 < 12.1),11,0))))))))))),IF(B7 < 2501,1,IF(AND(B7 > =2501,B7 < 3001),2,IF(AND(B7 > =3001,B7 < 3501),3,IF(AND(B7 > =3501,B7 < 4001),4,IF(AND(B7 > =4001,B7 < 4501),5,IF(AND(B7 > =4501,B7 < 5001),6,IF(AND(B7 > =5001,B7 < 5501),7))))))))

  8. I am trying to get a value returned based on a table of information with ranges. Below is the table I am working with. It is the bags requirements for a house foundation based on the square footage of the house slab, and the average height of the slab. I want to be able to return a value based on the two separate inputs and return the correct row and column.

    Example: the house slab is 3073 sf (this is B7 in the spreadsheet) and the average height is 4.08 ft (this is B12 in the spreadsheet). That should come out to be 15 (third column, fourth row).

    Height 2000-2500 2500-3000 3000-3500 3500-4000 4000-4500 4500-5000 5000-5500
    1.0-2.0 8 9 12 14 15 16 17
    2.0-3.0 9 11 13 15 16 17 19
    3.0-4.0 10 13 14 15 17 19 20
    4.0-5.0 11 14 15 16 19 20 22
    5.0-6.0 12 15 16 17 20 22 22
    6.0-7.0 13 16 17 18 22 22 23
    7.0-8.0 15 17 18 19 22 23 24
    8.0-9.0 16 18 19 19 23 24 26
    9.0-10.0 17 19 20 20 24 26 26
    10.0-11.0 18 20 21 22 26 26 27
    11.0-12.0 19 21 22 22 26 27 28

    Note: The table in my worksheet spans G24:N35

    The problem I am getting into is that I don't have specific values I am checking for along the rows and columns, but ranges in both. The current formula I have is below, but I am getting a #VALUE! error due to the 4.08 average height getting missed in my formula. Is there a better way to format this formula to manage all the ranges of the two values I am needing to check in the table or am I really stuck with all the nested IF's?

    =@INDEX(H25:N35,IF(B12=2.1,B12=3.1,B12=4.1,B12=5.1,B12=6.1,B12=7.1,B12=8.1,B12=9.1,B12=10.1,B12=11.1,B12<12.1),11,0))))))))))),IF(B7=2501,B7=3001,B7=3501,B7=4001,B7=4501,B7=5001,B7<5501),7))))))))

    • Hmm, the comment formatting broke my formula...that's not what it's suppose to read as. Maybe this will work.

      =@INDEX(H25:N35, IF(B12=2.1 , B12=3.1 , B12=4.1 , B12=5.1 , B12=6.1 , B12=7.1 , B12=8.1 , B12=9.1 , B12=10.1 , B12=11.1 , B12<12.1) , 11 , 0))))))))))) , IF(B7=2501 , B7=3001 , B7=3501 , B7=4001 , B7=4501 , B7=5001 , B7<5501) , 7))))))))

    • Hello!
      Write the first line as 2000 2500 3000 etc.
      Write the first column as 1 2 3 4 5, etc. If 4.08 is written in I1, in 3072 it is written in I2, then you can use the formula

      =VLOOKUP(I1, A2:H12, MATCH(I2, A1:H1, 1), 1)

      Please check out this article to learn how to Vlookup based on row and column values.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      • Much cleaner formula. Works perfectly, thanks!

  9. Excellent explanation and detail - thank you for posting!

  10. Hi, I have 2 columns that has Performance rating of 2 years. Say,
    Column A = 2 Column B =1 column C: an amount 3000 This will be in different combinations like, 1,2, 1,3, 2,1 etc for different employees

    I need to calculate :
    If column a=1, column b=2, then it should fetch Column C *2+200
    If column a=2, column c=3, then it should fetch column C* 0.5+ 300

    Similarly, different combinations of ratings for 2 years should fetch a value where different formulas are applied. How do I do that?

  11. Hi, thank you for explaining the Index/match function.

    I get the formula to work outside of 'tables', but not between tables.

    Formula is written like: {=Index(Cardata[emissions];Match(1;(Form[fueltype]=Cardata[fueltype])*(Form[cartype]=Cardata[cartype]);0))}

    It should return a corresponding emission number.

    ***Is there another way to make this formula work between tables?***

    Much appreciated!

    Rolf

    • Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      Is your formula not working? Please describe the problem in more detail.

  12. This formula worked for me.

    SUM(COUNTIFS($A$2:$A$10,H13,$B$2:$B$10,I13,$C$2:$C$10,J13,INDEX($D$2:$J$10,,MATCH($J$14,$D$1:$J$1,0)),{"=0","**"}))

  13. can reverse back the formula?
    which mean I put some price, I know the product from which region.

  14. Thank you for prompt reply.

    Data Sheet
    HDR-1 HDR-2 HDR-3 HDR-4 HDR-5 HDR-6
    ST BG DC 60 NA 35
    GT CG DB 20 40 40
    ST BG DC NA 60 90
    ST CG DB 30 20 NA
    ET BG DC 30 20 20
    ST BG DC 50 10 10

    Below Cell Values need to be Match in columns and header of Data sheet

    ST BG DC HDR-4

    HDR-4 Count Result = 2

    • Hi,
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =SUM(IFERROR((A2:A10=$H$8)*(B2:B10=$I$8)*(C2:C10=$J$8) * (ISNUMBER(INDIRECT(ADDRESS(2,MATCH($J$14,A1:J1,0))&":"&ADDRESS(2,MATCH($J$14,A1:J1,0))&"00"))),0))

      $J$14 -- HDR-4
      H8:J8 -- ST BG DC

      I hope it’ll be helpful.

      • Thanks Alexander for the answer, unfortunately, this formula doesn't work for me. I will try to explain this issue in more detail. Suppose I have data from A2 to J10 and A1 to J1 is the data header (HRD-1, HRD-2....HRD-10 ).

        Now I will describe the criteria.
        Assume that 1st Criteria is in cell H13 to J13, as stated in formula (A2:A10=$H$13)*(B2:B10=$I$13)*(C2:C10=$J$13). And 2nd criteria is in J14, as stated in the formula MATCH($J$14,A1:J1,0).

        In K14 I want to count the total number of numeric data of each column (HD-4 column {i.e D2:D10
        }...... to........ HD-10 column {i.e J2:J10}) by keep changing cell criteria in H13 to J13 as well as the header criteria in cell J14.
        Hope you understand my description.

  15. HI,
    Can someone help me to add INDEX(Data!A1:P1,MATCH(J14,Data!A1:J1,0)) formula to COUNTIFS(Data!A2:A4000,H8,Data!B2:B4000,I8,Data!C2:C4000,J8,Data!F2:F4000,">0"). I am trying to count numerical cells where Header and column Criteria is being matched.

    Thank You

    • Hello!
      The COUNTIFS function uses only range references as criteria_range. Therefore, you cannot use the INDEX function for this. So that I can give you advice on how to write a different formula, please describe your problem in more detail.

  16. Hello,

    Is there any reason why the function below will not work for "INDEX MATCH with several criteria - formula example"? I still get $115 when I use the formula below (without pressing Ctrl + Shift + Enter).

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

    Thank you!

    • Hi,
      Unfortunately, without seeing your data it is difficult to give you any advice.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

      • Hi Alexander,

        Appreciate the reply! I was referring to the first example shown on this page.

        The first formula is what Svetlana shows to get to the answer, but I was wondering why an array formula needs to be used if the second formula also gets me to the correct answer.

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

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

        Thanks,
        Scott

        • Hello!
          Two solutions are always better than one. You can use any formula. However, Microsoft warns that the string concatenation operation is computationally intensive. Therefore your second formula with a lot of data will be slower than the others.

          • Appreciate the help!

  17. Hi, I want to search a cell range for a string and place the matching cells into a new column.

    For example,
    In Cells A1:A5 below... Column P

    AAL Jan 08 2021 18.5 Call AAL Jan 08 2021 18.5 Call
    CBD DENVER INC (CBDD) DAL Dec 31 2020 42.0 Call
    DAL Dec 31 2020 42.0 Call
    FCEL Dec 18 2020 8.0 Put
    SANUWAVE HEALTH INC (SNWV)

    I want to find all the "Call" strings and place them sequentially in column P (shown above).
    How can I use the index and if macros to do this? Or how would you do this?

    Thanks!

  18. Your example file doesn't work lol - there is #Value! in cell with result if i push ENTER to recalculate your formula.

    • Hello George,

      If it's an array formula, then you should press Ctrl + Shift + Enter to recalculate it. I've just checked all the examples in our sample workbook and all 3 formulas recalculated just fine.

      If the error persists on your side, please let me know which example does not work and what Excel version you are using.

  19. I'm having issues with Index + Match working correctly. Here's what I am trying to do:
    > find a Number in a row based on two separate search criteria, each in its own row:

    Criteria 1 is to find a specific Date in row range B1:G1
    Criteria 2 is to find a specific Word in row range B2:G2
    The Number I need to find is in the Column of row 3 where Criteria 1& 2 are matched.

    Thanks in advance.

  20. Hello Alexander,
    Please help, i am stuck at a report which can be understood from below table eg:
    i need to index marks in similar table with only those students names who have marks less than 40, along with marks in similar column but only less than 40. I am not sure which function to apply but trying
    index only without success so far.

    Student Math History English Science
    A 35 70 85 20
    B 55 64 30 81
    C 47 49 40 79
    D 62 52 94 27
    E 15 35 50 32
    F 38 75 29 19

    Thanx in advance

    • Hello!
      If I got you right, please check out this article to learn how to VLOOKUP multiple values in Excel with one or more criteria.
      To help you write your formula, describe in detail the result you would like to get from your data.

      • Thanks for your help Alexander, the result i would want is some thing like below table, since
        only those marks are shown which are less than 40 (i have put - to represent blanks), and only those student names should appear
        whose marks are less than 40, like in below table name of student C is not there. ( I think in vlookup we have to put all the names)

        Student Math History English Science
        A 35 - - 20
        B - - 30 -
        D - - - 27
        E 15 35 - 32
        F 38 - 29 19

        Thanks in advance and apologies to trouble you again.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)