INDEX MATCH for Multiple Criteria in Rows and Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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. Using INDEX-MATCH with OR Type Multiple Criteria in Rows and Columns

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.


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.


2. Applying INDEX-MATCH with 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.


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.


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 to use the INDEX MATCH formula with multiple criteria in different sheet.

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


Download Practice Workbook

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


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.


<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo