# Excel INDEX MATCH with Multiple Criteria and Multiple Results

Get FREE Advanced Excel Exercises with Solutions!

Excel INDEX MATCH with multiple criteria and multiple results means getting multiple outputs based on multiple conditions using Excel INDEX and MATCH functions.

In this Excel tutorial, you will learn how to get multiple results using the INDEX-MATCH formula with multiple criteria in Excel.

The INDEX-MATCH formula is very useful in finding any value based on any criteria in Excel. But sometimes you may need to extract multiple results based on multiple criteria in Excel. For example, you can find all the available Models for a given Brand name and Device type from the following dataset. Here, our criteria are Brand and Device. So, we used the INDEX and MATCH functions with some other functions to get multiple results based on these criteria. We have shown several cases in the following sections. You can get multiple results based on multiple criteria from the same column, or based on multiple criteria from different columns. We have also shown how to use Excel’s FILTER and COUNT functions instead of INDEX and MATCH functions to get multiple outputs based on multiple conditions.

## 1. Get Multiple Results Using INDEX-MATCH Formula Based on 2 Criteria (From the Same Column)

In our first method, we will choose 2 criteria from the same column.

Consider the following dataset. We have an employee database that contains the Employee ID, Full Name, Department, Designation, and Annual Salary of a few employees.

We will choose 2 criteria from the Department column here. Let’s find the list of all the employees under the Full Name column, who are from the Sales and Finance Department.

Along with the INDEX and MATCH functions, we also require the IFERROR, SMALL, IF, ISNUMBER, ROW, and ROWS functions here. Apply the following steps to get the target list of employees who are from the Sales and Finance departments.

Steps:

• In Cell I10, insert the following formula.
`=IFERROR(INDEX(\$C\$6:\$C\$20,SMALL(IF(ISNUMBER(MATCH(\$D\$6:\$D\$20,\$I\$6:\$I\$7, 0)),MATCH(ROW(\$D\$6:\$D\$20),ROW(\$D\$6:\$D\$20)),""),ROWS(\$D\$6:D6))),"")`
• After inserting the formula, press the Enter key. You will get the first match value for the given conditions.
• Now, hover your mouse pointer over the bottom right corner of cell E6. You will find the Fill Handle icon.
• Drag down the Fill Handle icon to get all matched values for the given conditions.
• Now, if you change the department names, the matched Full Names will change as well. Note: Here, we have created a Drop-down list for criteria and applied Conditional Formatting to add borders in non-blank cells. You can choose any criteria from the drop-down list.

## 2. Get Multiple Results Using INDEX-MATCH Formula Based on 2 Criteria (From Different Columns)

Now, we will select 2 criteria from different columns to get multiple results.

Consider the following dataset. We have a database that contains the Brand name, Device type, Model number, Manufacturing Location, and Price of a few computers.

We want the list of Models for the Brand Codemy and Device type Notebook. Here, our criteria are from different columns which are “Brand” and “Device”.

Apply the steps below to find the target results.

Steps:

• Insert the following formula in Cell I10.
`=IFERROR(INDEX(\$D\$6:\$D\$20, SMALL(IF(ISNUMBER(MATCH(\$B\$6:\$B\$20, \$I\$6, 0)*MATCH(\$C\$6:\$C\$20,\$I\$7,0)), MATCH(ROW(\$B\$6:\$B\$20), ROW(\$B\$6:\$B\$20)), ""), ROWS(\$D\$6:D6))),"")`

This formula is similar to the formula used in the previous section. The only difference is, within the ISNUMBER function, we have used two MATCH functions (for our two criteria) instead of one.

• Then, press the Enter key. You will get the first matched value.
• After that, drag down the Fill Handle icon to get all the results.

## 3. Get Multiple Results Using INDEX-MATCH Formula Based on 3 Criteria

In this method, we will combine the concepts of the previous two methods to get multiple results using the INDEX-MATCH formula based on 3 criteria that are from the same or different columns.

Let’s find the models of Gamind or Codemy brand and Notebook device type.

Apply the following steps.

Steps:

• Insert the following formula in I12.
`=IFERROR(INDEX(\$D\$6:\$D\$20, SMALL(IF(ISNUMBER(MATCH(\$B\$6:\$B\$20, \$I\$6:\$I\$7, 0)*MATCH(\$C\$6:\$C\$20,\$I\$9,0)), MATCH(ROW(\$B\$6:\$B\$20), ROW(\$B\$6:\$B\$20)), ""), ROWS(\$D\$6:D6))),"")`
• Then, press the Enter key.
• Finally, copy the formula down to get the full list of Model numbers.

## 4. Using FILTER and COUTNIF Functions (An Alternative to INDEX-MATCH)

The INDEX-MATCH formulas used in the previous methods were very complex. You can use the combination of FILTER and COUNTIF functions as a simple alternative to that formula.

Note: The FILTER function is available only in Office 365.

Here, we will solve the problems described in previous sections using the FILTER and COUNTIF Functions.

In the dataset of method 1, multiple criteria are from the same column. In such cases, you can apply the following formula and press the Enter key to get the results.

`=FILTER(C6:C20,COUNTIF(I6:I7,D6:D20))`

And, in the dataset of method 2, the multiple criteria are from different columns. In such instances, you can skip the COUNTIF function and apply the following formula.

`=FILTER(D6:D20,(B6:B20=I6)*(C6:C20=I7))`

This concludes our tutorial on how to find multiple results using the INDEX-MATCH formula with multiple criteria in Excel. We chose multiple criteria from the same column and from different columns. This tutorial also showed the FILTER function as a simple alternative to using the INDEX-MATCH formula to get multiple results with multiple criteria in Excel. We hope that this article was helpful to you. Let us know your feedback in the comment section.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. 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.

1. Reply 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)))

I have a condition where I need to match two (sometimes three) criterias and get multiple matching results.

• Reply 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. Reply Hi,
Can you show me the formula if you change the criteria to dates?

• Reply 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

• Reply 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

• 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

3. Reply Howdy.

Is it possible to use wildcards with the second 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))

• Reply Hi ALLISON,
If you want to use wildcards with the second formula, you need to modify the dataset a little bit. Let’s say, we have short form of country name in the “Country” column. Our aim is to find the name of the person aged “38” and whose country is “India“. You can use 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)) We have used the wildcards character ampersand (*) in the second COUNTIF portion: COUNTIF(\$G\$6,\$D\$5:\$D\$17&”*”)
As we want to find multiple output so it will result from an array. So, the \$D\$5:\$D\$17 acts as criteria as we can use wildcards character in criteria. This formula will match the short form mentioned in the country column and match it with criteria and extract the Name.

Regards
Rafiul Hasan
Team ExcelDemy Advanced Excel Exercises with Solutions PDF  