# Using the INDEX MATCH Functions for Multiple Criteria in Rows and Columns in Excel – 2 Methods

The sample dataset showcases the Annual Examination Record of a school named The Rose Valley Kindergarten,Â Student Names in column B and their marks in History, Math, and English in columns C, D, and E.

## Method 1- Using the INDEX-MATCH function with OR Type Multiple Criteria in Rows and Columns

### 1.1 INDEX and MATCH Functions with an Array Formula

Use of the INDEX and MATCH functions with an Array formula.

Steps:

• Select E15 and enter the following formula.

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

Formula Breakdown:

• With the MATCH function, the 3 criteria: Marks in History, Math, and English are matched with ranges C5:C13, D5:D13, and E5:E13.
• The match type is 1, which gives an exact match.
• The INDEX function gets the name of the student from B5:B13.
• Press Enter key to find the name of the first student with more than 95 in any subject.

### 1.2 INDEX and MATCH with Non-Array

Steps:

• Select E15 and enter the following formula.

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

• Press Enter to see the final output.

## Method 2 – Applying the INDEX-MATCH with AND Type Multiple Criteria in Rows and Columns in Excel

### 2.1 INDEX and MATCH Functions with anArray

Steps:

• Select E15 and enter the following formula.

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

Formula Breakdown:

• The MATCH function has 3 criteria: Marks in History, Math, and English are matched with their corresponding ranges, C5:C13, D5:D13, and E5:E13.
• The match is found as 1: an exact match that meets all the conditions.
• The INDEX function provides the name of the student from B5:B13.
• The name of the first student with more than 90 in all 3 subjects will be displayed.

### 2.2 Using INDEX and MATCH with Non-ArrayÂ

Steps:

• Select E15 and enter the following formula.

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

• Press Enter to see the final output.

## INDEX MATCH for Multiple Criteria in Different Sheets in Excel

Steps:

• Click D4.
• Enter the following formula.

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

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

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF