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.

2 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.

Leave a reply

ExcelDemy
Logo