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.

Sample Dataset of INDEX MATCH for Multiple Criteria in Rows and Columns in Excel


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.

=INDEX(B5:B13,MATCH(TRUE,(((C5:C13)>95)+((D5:D13)>95)+((E5:E13)>95))>0,0))

INDEX and MATCH Functions with Array Formula for Multiple Criteria of OR Type in Excel

🔎 Formula Breakdown:

  • Using the MATCH function, the 3 criteria: Marks in History, Math, and English are matched with ranges C5:C13, D5:D13, and E5:E13, respectively, from the dataset.
  • Here, the match type is 1, which gives an exact match.
  • Lastly, using the INDEX function, it gets the name of the student from the range B5:B13.
  • Lastly, hit the Enter key in order to find the name of the first student with more than 95 in any subject.

Read More: Examples with INDEX-MATCH Formula in Excel (8 Approaches)


Similar Readings


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.

=INDEX(B5:B13,MATCH(TRUE,INDEX((((C5:C13)>95)+((D5:D13)>95)+((E5:E13)>95))>0,0,1),0))

INDEX and MATCH with Non-Array for OR Type in Excel

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

Read More: INDEX MATCH with Multiple Criteria in a Different Sheet (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.

=INDEX(B5:B13,MATCH(1,(((C5:C13)>90)*((D5:D13)>90)*((E5:E13)>90)),0))

AND Type Multiple Criteria in Rows and Columns with Array in Excel

🔎 Formula Breakdown:

  • Firstly, the MATCH function has the 3 criteria: Marks in History, Math, and English are matched with their corresponding ranges, C5:C13, D5:D13, and E5:E13, from the given dataset.
  • After that, the match is found as 1and it gives an exact match that satisfies all the conditions.
  • Finally, the INDEX function provides the name of the student from the range B5:B13 for that match.
  • 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.

=INDEX(B5:B13,MATCH(1,INDEX((((C5:C13)>90)*((D5:D13)>90)*((E5:E13)>90)),0,1),0))

Non-Array Using INDEX and MATCH in Excel for AND Type Multiple Criteria

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

=INDEX(Dataset!B5:B13,MATCH(TRUE,(((Dataset!C5:C13)>95)+((Dataset!D5:D13)>95)+((Dataset!E5:E13)>95))>0,0))

INDEX MATCH for Multiple Criteria in Different Sheets in Excel

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: Excel Index Match single/multiple criteria with single/multiple results


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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo