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.

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

Priceof a product withcriteria. One is the2Product Name, another criterion is thedate. The formula we used in cellI5is 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 Rangeon 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()-6in this formula. To understand it, you have to have a clear concept of theINDEX functionand its arguments. Syntax of theINDEX functionin 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()-6is theargument of thecolumn_numINDEX function.Now, look at the worksheet. At first, we want to get the

Ageof this person. The output range is inRow 8. So, for cellI8, theROW functionwill return us8. After that, subtracting6from this, we get2. Then, look at the array which isB4:F17. In this array, which column contains the Ages?? Obviously, thesecondcolumn. That’s howROW()-6gives us the column number to match in the array.Similarly, to find the

Sexin cellI9, we used the same formula. Here,ROW()-6returns us3. And the3rdcolumn of the array contains theSexof 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 RafidExcel & VBA Content Developer

Team ExcelDemy