Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel INDEX MATCH with Multiple Criteria and Multiple Results

In this article, we are going to discuss an important topic which is “Excel INDEX MATCH with multiple criteria to find multiple results”. Finding the exact result for this is very difficult, so we are going to show different scenarios for this. We are going to see how the INDEX and MATCH functions can be used together to provide single/multiple results from multiple and single criteria. Finally, at the end of this article, you will get to see all possible combinations of INDEX and MATCH functions together.


Download Practice Workbook


4 Ways to Use INDEX and MATCH Functions with Multiple Criteria to Find Multiple Results in Excel

Let’s say we have a table containing various information about some people who are going to attend a conference. The table contains the information of the Name, Age, Sex, Country, and ID of these people.

Now, we will show you how to use INDEX and MATCH functions with single/multiple criteria to find single/multiple results in Excel using this dataset.

Ways to Use INDEX and MATCH Functions with Multiple Criteria to Find Multiple Results in Excel


1. Using SMALL and ROWS Functions with INDEX and MATCH Functions to Find Multiple Results with Multiple Criteria

Firstly, I will show you how we can use multiple criteria as the reference using the SMALL and ROWS functions with INDEX and Match functions to find out multiple results.

For Example, from the given dataset you want to select 2 candidates based on their ID number. So, their ID numbers will be the criteria in this case for choosing them.

Using SMALL and ROWS Functions with INDEX and MATCH Functions to Find Multiple Results with Multiple Criteria in Excel

Follow the steps given below to find the corresponding Names of these ID numbers.

Steps:

  • Firstly, select Cell F9 and insert the following formula.
=INDEX($B$5:$B$17, SMALL(INDEX(MATCH($F$5:$F$6, $C$5:$C$17, 0), ), ROWS($D$4:D4)))

Formula Breakdown

  • ROWS($D$4:D4)—–> The ROWS function returns the number of rows of a given Cell range.
    • Output: {1}
  • MATCH($F$5:$F$6, $C$5:$C$17, 0)—–> The MATCH function returns the position of the look_up value.
    • Output: {8;10}
  • INDEX(MATCH($F$5:$F$6, $C$5:$C$17, 0), )—–> The INDEX function returns value of a given Cell range.
    • INDEX({8;10}, )—–>turns into
      • Output:{8;10}
  • SMALL(INDEX(MATCH($F$5:$F$6, $C$5:$C$17, 0), ), ROWS($D$4:D4))—–> The SMALL function returns the nth smallest value of a range.
    • SMALL({8;10},1))—–>turns into
      • Output:{8}
  • INDEX($B$5:$B$17, SMALL(INDEX(MATCH($F$5:$F$6, $C$5:$C$17, 0), ), ROWS($D$4:D4)))—–>turns into
    • INDEX($B$5:$B$17, 8)—–>turns into
      • Output:{Jobayer}

  • Then, press ENTER to get the 1st Name.
  • After that, drag down the Fill Handle tool to get the 2nd Name.

  • Finally, you will get multiple Names using multiple ID numbers as criteria.

Excel INDEX MATCH Functions with Multiple Criteria to Find Multiple Results Using SMALL and ROW Functions


2. Use of Combined Functions to Get Multiple Results with Multiple Criteria in Excel

Now, we will show you how you can use the INDEX, SMALL, IF, COUNTIF, ROW, MIN, and COLUMN functions to find results with multiple criteria in Excel.

For example, from the given dataset you want to find candidate Names based on their Age and Country. Go through the steps given below to do it on your own.

Steps:

  • Firstly, select Cell G9 and insert the following formula.
=INDEX($B$5:$B$17, SMALL(IF(COUNTIF($F$6, $C$5:$C$17)*COUNTIF($G$6, $D$5:$D$17), ROW($B$5:$D$17)-MIN(ROW($B$5:$D$17))+1), ROW(A1)), COLUMN(A1))

Use of Combined Functions to Get Multiple Results with Multiple Criteria in Excel

Formula Breakdown

  • ROW(A1)—–> The ROW function returns the row number of a given cell or cell range.
    • Output: {1}
  •  ROW($B$5:$D$17)—–> turns into
    • Output:{5;6;7;8;9;10;11;12;13;14;15;16;17}
  • COLUMN(A1)—–> The COLUMN function returns the column number of a given Cell.
    • Output: {1}
  • COUNTIF($F$6, $C$5:$C$17)—–> The COUNTIF function returns the number of cells for a given condition.
    • Output:{1;0;1;0;0;1;0;0;0;0;0;0;0}
  • COUNTIF($G$6, $D$5:$D$17)—–> turns into
    • Output:{1;1;1;0;0;1;0;0;0;0;0;0;0}
  • MIN(ROW($B$5:$D$17))—–> The MIN function returns the least value in a range.
    • MIN({5;6;7;8;9;10;11;12;13;14;15;16;17})—–> turns into
      • Output: {5}
  • IF(COUNTIF($F$6, $C$5:$C$17)*COUNTIF($G$6, $D$5:$D$17), ROW($B$5:$D$17)-MIN(ROW($B$5:$D$17))+1)—–> The IF function returns a value if the condition is TRUE and returns a different value if the condition is FALSE.
    • IF({1;0;1;0;0;1;0;0;0;0;0;0;0}, {1;2;3;4;5;6;7;8;9;10;11;12;13})—–> turns into
      • Output:{1;FALSE;3;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
  •  SMALL(IF(COUNTIF($F$6, $C$5:$C$17)*COUNTIF($G$6, $D$5:$D$17), ROW($B$5:$D$17)-MIN(ROW($B$5:$D$17))+1), ROW(A1)))—–> The SMALL function returns the nth smallest value of a range.
    • SMALL({1;FALSE;3;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, {1})—–> turns into
      • Output: {1}
  • INDEX($B$5:$B$17, SMALL(IF(COUNTIF($F$6, $C$5:$C$17)*COUNTIF($G$6, $D$5:$D$17), ROW($B$5:$D$17)-MIN(ROW($B$5:$D$17))+1), ROW(A1)), COLUMN(A1))—–> The INDEX function returns value of a given Cell range.
    • INDEX($B$5:$B$17, {1}, {1})—–> turns into
      • Output: {Sidhhart}

  • After that, press ENTER to get the 1st Name.
  • Then, drag down the Fill Handle tool to get all the Names according to the criteria.

  • Finally, you will get multiple Names using Age and Country as criteria.

Excel INDEX MATCH Functions with Multiple Criteria to Find Multiple Results Using Combined Functions


3. Using INDEX and MATCH Functions with Single Criteria and Multiple Results

Moreover, the INDEX and MATCH functions can be used to show multiple results with a single criterion. Here I will show you 2 processes whereby referencing the Country`s name you are going to get the candidate’s name. Another one will show you how you can get all the information of a candidate based on his Name only.


3.1. Using INDEX, MATCH, and ROW Functions

Firstly, you will see how by referencing only Names you can get all information about the candidate like Age, Sex, Country, and ID by using the INDEX, MATCH, and ROW functions.

Therefore, follow the steps given below.

Steps:

  • Firstly, select Cell I8 and insert the following formula.
=INDEX($B$4:$F$17,MATCH($I$5,($B$4:$B$17),0),ROW()-6)

Using INDEX and MATCH Functions with Single Criteria and Multiple Results in Excel

Formula Breakdown

  • ROW()—–> The ROW function returns the row number of a given Cell.
    • Output: {8}
  • MATCH($I$5,($B$4:$B$17),0)—–> The MATCH function returns the position of the look_up value.
    • Output: {5}
  • INDEX($B$4:$F$17,MATCH($I$5,($B$4:$B$17),0),ROW()-6)—–> The INDEX function returns the value of a given Cell range.
    • INDEX($B$4:$F$17,5,{8}-6)—–>turns into
      • Output:{35}

  • After that, press ENTER to get the value of Age of the selected candidate.
  • Then, drag down the Fill Handle tool to get all the other information about that candidate.

  • Finally, you will have multiple information of that candidate using the Name as criteria.


3.2. Using Combined Functions

Secondly, we will show how you can find multiple Names of candidates using the Country name as criteria using the INDEX, SMALL, IF, ISNUMBER, MATCH, ROW, ROWS function.

So, now, go through the steps given below.

Steps:

  • Firstly, select Cell F8 and insert the following formula.
=INDEX($B$5:$B$17,SMALL(IF(ISNUMBER(MATCH($C$5:$C$17,$F$5,0)),MATCH(ROW($C$5:$C$17),ROW($C$5:$C$17)),""),ROWS($D$4:D4)))

Formula Breakdown

  • ROWS($D$4:D4)—–> The ROWS function returns the number of rows of a given cell or cell range.
    • Output: {1}
  •  ROW($C$5:$C$17)—–> turns into
    • Output:{5;6;7;8;9;10;11;12;13;14;15;16;17}
  • MATCH($C$5:$C$17,$F$5,0)—–>The MATCH function returns the position of the look_up value.
    • Output:{#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;1;1;1;1;#N/A;#N/A;#N/A}
  • MATCH(ROW($C$5:$C$17),ROW($C$5:$C$17))—–> turns into
    • Output:{1;2;3;4;5;6;7;8;9;10;11;12;13}
  • ISNUMBER(MATCH($C$5:$C$17,$F$5,0))—–>The ISNUMBER function returns if the given range is a number or not.
    • ISNUMBER({#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;1;1;1;1;#N/A;#N/A;#N/A})—–> turns into
      • Output:{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}
  • IF(ISNUMBER(MATCH($C$5:$C$17,$F$5,0)),MATCH(ROW($C$5:$C$17),ROW($C$5:$C$17)),””)—–> The IF function returns a value if the condition is TRUE and returns a different value if the condition is FALSE.
    • IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13},””)—–> turns into
      • Output:{“”;””;””;””;””;””;7;8;9;10;””;””;””}
  • SMALL(IF(ISNUMBER(MATCH($C$5:$C$17,$F$5,0)),MATCH(ROW($C$5:$C$17),ROW($C$5:$C$17)),””),ROWS($D$4:D4))—–> The SMALL function returns the nth smallest value of a range.
    • SMALL(IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},MATCH({5;6;7;8;9;10;11;12;13;14;15;16;17},ROW($C$5:$C$17)),””),ROWS($D$4:D4))SMALL({“”;””;””;””;””;””;7;8;9;10;””;””;””},1)—–> turns into
      • Output:{7}
  • INDEX($B$5:$B$17,SMALL(IF(ISNUMBER(MATCH($C$5:$C$17,$F$5,0)),MATCH(ROW($C$5:$C$17),ROW($C$5:$C$17)),””),ROWS($D$4:D4)))—–> The INDEX function returns the value of a given Cell range.
    • INDEX($B$5:$B$17,7)—–> turns into
      • Output:{Ahmed}

  • Then, press ENTER and drag down the Fill Handle tool to get all the Names of the candidates from that Country.

  • Finally, you will get multiple Names using Country as criteria.


4. Applying INDEX and MATCH Functions for Multiple Criteria and Single Result

Finally, you will find a way to find a single result for multiple criteria using the INDEX and MATCH functions in Excel.

Here, we will show you how you can find the ID of a candidate using Name, Sex, and Country as criteria.

Applying INDEX and MATCH Functions for Multiple Criteria and Single Result in Excel

Therefore, follow the steps given below to do it on your own.

Steps:

  • Firstly, select Cell H10 and insert the following formula.
=INDEX(E5:E17,MATCH(1,(H5=B5:B17)*(H6=C5:C17)*(H7=D5:D17),0))

Formula Breakdown

  • MATCH(1,(H5=B5:B17)*(H6=C5:C17)*(H7=D5:D17),0)—–> The MATCH function returns the position of the look_up value.
    • Output: {4}
  • INDEX(E5:E17,MATCH(1,(H5=B5:B17)*(H6=C5:C17)*(H7=D5:D17),0))—–> The INDEX function returns the value of a given Cell range.
    • INDEX(E5:E17,4)—–>turns into
      • Output:{ICME-1014}

  • Then, press ENTER to get the ID of the candidate using multiple criteria.


Using FILTER and COUNTIF Functions to Find Multiple Results with Multiple Criteria in Excel

Additionally, we will show you how you can use the FILTER and COUNTIF functions to find multiple results with multiple criteria in Excel.

Here, we will use the 2 Names of the candidates as criteria and find their ID numbers as result.

So, now go through the steps given below to do it on your own.

Steps:

  • Firstly, select Cell F9 and insert the following formula.
=FILTER(C5:C17,COUNTIF(F5:F6,B5:B17))

Using FILTER and COUNTIF Functions to Find Multiple Results with Multiple Criteria in Excel

Formula Breakdown

  • COUNTIF(F5:F6,B5:B17)—–> The COUNTIF function returns the number of cells for a given condition.
    • Output:{0;0;0;0;0;1;0;1;0;0;0;0;0}
  • FILTER(C5:C17,COUNTIF(F5:F6,B5:B17))—–> The FILTER function returns the filtered data.
    • FILTER(C5:C17,{0;0;0;0;0;1;0;1;0;0;0;0;0})—–> turns into
      • Output:{“ICME-1016″;”ICME-1018”}

  • After that, press ENTER to get multiple ID numbers using their Names as criteria.


Practice Section

Finally, in this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Conclusion

So, in this article, you will find 4 ways to use the INDEX and MATCH functions to find multiple results with multiple criteria in Excel. Use any of these ways to accomplish the result in this regard. We hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. However, let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

6 Comments
  1. Hi,

    In the array formula =INDEX($A$2:$A$14,SMALL(IF(ISNUMBER(MATCH($D$2:$D$14,$G$3,0)),MATCH(ROW($D$2:$D$14),ROW($D$2:$D$14)),””),ROWS($F$1:F1)))

    What should I do if I need to add one more criteria, for example Country and Sex. Please help.
    I have a condition where I need to match two (sometimes three) criterias and get multiple matching results.

    Thanks in advance.

    • Hi Deepak!
      The following formula can match two criteria and return multiple matches:
      =INDEX($E$2:$E$14, SMALL(IF(COUNTIF($G$2, $C$2:$C$14)*COUNTIF($H$2, $D$2:$D$14), ROW($A$2:$E$14)-MIN(ROW($A$2:$E$14))+1), ROW(A1)), COLUMN(A1))
      Here,
      $E$2:$E$14= Column to return matches
      $G$2 and $H$2= Criteria 1 and 2
      $C$2:$C$14 and $D$2:$D$14= The columns of main data respective to criteria 1 and 2
      In a similar way, you can add any number of criteria.

      Thank you for being with us.

  2. Hi,
    Can you show me the formula if you change the criteria to dates?

    • Hello KYLE,
      Obviously, you can do that for dates also. See the image below.

      Here, we retrieved the Price of a product with 2 criteria. One is the Product Name, another criterion is the date. The formula we used in cell I5 is the following.
      =INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0))
      You can go through the article How to Use INDEX MATCH with Multiple Criteria for Date Range on our website for an explanation of this formula and other methods to do the same task.
      Anyway, I hope that helps. You may follow our website, ExcelDemy, a one-stop Excel solution provider, to explore more. Happy Excelling.
      Regards,
      Shahriar Abrar Rafid
      Excel & VBA Content Developer
      ExcelDemy

      • Sir, kindly explain the last part of this formula – =INDEX($B$4:$F$17,MATCH($I$5,($B$4:$B$17),0),ROW()-6)

        I mean the ROW ()-6

        Thanks in advance.

        • Hello OLAWANDE,
          I get your question. It’s a pleasure to us that our readers read our content well and ask us questions if they don’t get it. Also, they give us positive feedback. Thanks, OLAWANDE.
          Now, getting back to your query. You wanted to know the purpose of ROW()-6 in this formula. To understand it, you have to have a clear concept of the INDEX function and its arguments. Syntax of the INDEX function in array form is like the following.
          =INDEX(array, row_num,[column_num])
          If you match this structure with the formula, you can easily perceive that ROW()-6 is the column_num argument of the INDEX function.
          Now, look at the worksheet. At first, we want to get the Age of this person. The output range is in Row 8. So, for cell I8, the ROW function will return us 8. After that, subtracting 6 from this, we get 2. Then, look at the array which is B4:F17. In this array, which column contains the Ages?? Obviously, the second column. That’s how ROW()-6 gives us the column number to match in the array.
          Similarly, to find the Sex in cell I9, we used the same formula. Here, ROW()-6 returns us 3. And the 3rd column of the array contains the Sex of the people.
          I think you understood now, how this part of the formula works. Thanks again for your beautiful comment. You may visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.
          Regards,
          Shahriar Abrar Rafid
          Excel & VBA Content Developer
          Team ExcelDemy

Leave a reply

ExcelDemy
Logo