INDEX MATCH for Multiple Criteria in Rows and Columns in Excel

Often, you may find yourself in a situation where you are working with a large array of data and need to find some unique values or texts but don’t have a specific identifier for this purpose. In this case, a vertical or horizontal lookup with several conditions is used to find the result. But, instead of using these functions, expert users normally apply the INDEX MATCH combination. The combination of the INDEX and MATCH functions is superior to VLOOKUP or HLOOKUP in many ways. Moreover, the INDEX MATCH formula can look up a value with multiple criteria on different sheets and return the result in another worksheet. In this article, I will show you 2 ideal examples of INDEX MATCH functions for multiple criteria in rows and columns in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

2 Ideal Examples of INDEX MATCH for Multiple Criteria in Rows and Columns in Excel

In this part, I will show you 2 ideal examples of INDEX MATCH functions for multiple criteria. For the purpose of demonstration, I have used the following sample dataset. We have the Annual Examination Record of a school named The Rose Valley Kindergarten. However, we have the Student Names in column B and their marks in History, Math, and English in columns C, D, and E, respectively.

1. Multiple Criteria of OR Type in Rows and Columns in Excel

In the beginning, I will discuss the multiple OR criteria. Generally, the OR type is used when an argument needs to satisfy any of the conditions. It is pretty easy to use. Usually, INDEX MATCH functions with multiple criteria of the OR type can be done in two ways, such as using the Array formula and the Non-Array formula. However, I have demonstrated both processes below with the same dataset.

1.1 INDEX and MATCH Functions with Array Formula

Initially, I will show the use of the INDEX and MATCH functions with the Array formula. It is quite handy to operate. However, you need to follow the steps below.

📌  Steps:

• First, select cell E15 and write down the following formula.

• Lastly, hit the Enter key in order to find the name of the first student with more than 95 in any subject.

Read More: INDEX-MATCH Formula to Generate Multiple Results in Excel

Similar Readings

• How to Use INDEX and Match for Partial Match (2 Ways)
• INDEX MATCH with 3 Criteria in Excel (4 Examples)
• INDEX MATCH across Multiple Sheets in Excel (With Alternative)
• Index Match Sum Multiple Rows in Excel (3 Ways)
• Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function

1.2 INDEX and MATCH with Non-Array

Furthermore, you can use the Non-Array formula and still receive a similar output. However, if you do not want to use the Array formula, you can utilize the Non-Array formula. Hence, read through the following steps.

📌  Steps:

• First of all, select cell E15 and insert the following formula.

• Finally, press the Enter key to get the final output.

Read More: Use INDEX MATCH for Multiple Criteria Without Array (2 Ways)

2. AND Type Multiple Criteria in Rows and Columns in Excel

Similarly, the AND type of multiple criteria can be completed by the array formula and Non-Array formula. Usually, the AND type is applied when an argument needs to satisfy all the conditions. For the purpose of demonstration, I will use the previous dataset. However, you need to go through the below section in order to complete the operation easily.

2.1 INDEX and MATCH Functions with Array

First of all, I will accomplish this using an Array formula. However, it is much similar to the OR type and simple to use. Hence, follow the instructions below in order to complete the operation properly.

📌  Steps:

• Initially, click on cell E15 and write the formula below.

• Similarly, the name of the first student with more than 90 in all 3 subjects will appear as in the below image.

Read More: Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)

2.2 Non-Array Using INDEX and MATCH

Last but not least, I will show the use of the INDEX and MATCH functions with multiple criteria of the AND type with the Non-Array formula. Similarly, go through the following steps in order to get the final result.

📌  Steps:

• Firstly, select cell E15 and write down the formula mentioned below.

• Lastly, hit the Enter key to receive the final output.

Read More: How to Match Multiple Criteria from Different Arrays in Excel

INDEX MATCH for Multiple Criteria in Different Sheets in Excel

Fortunately, the INDEX MATCH formula is quite efficient when you are finding data with multiple criteria for both columns and rows in different sheets. In this part, you’ll get to learn how we can use the INDEX and MATCH functions across multiple worksheets with appropriate illustrations. Hence, follow the below steps.

📌  Steps:

• First, click on cell D4.
• Secondly, write the following formula.

Here, “Dataset” is the name of the sheet from which you want to extract data.

• Similarly, you can change the sheet’s name and condition and receive your desired result.

Read More: INDEX MATCH Formula with Multiple Criteria in Different Sheet

Conclusion

These are all the steps you can follow to apply the INDEX MATCH functions to multiple criteria in rows and columns in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.

Related Articles

• Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
• How to use INDEX & MATCH worksheet functions in Excel VBA
• Excel INDEX MATCH to Return Multiple Values in One Cell
• INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
• Index Function to Match and Return Multiple Values Vertically in Excel
• IF with INDEX-MATCH in Excel (3 Suitable Approaches)