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.

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

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

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

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

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

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

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

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

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

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 2In a similar way, you can add any number of criteria.

Thank you for being with us.