In this article, I’ll show you how you can use **IF** with **INDEX-MATCH **in Excel. The** IF **function**,** **INDEX **function**, **and **MATCH **function are three very important and widely used functions of Excel. While working in Excel, we often have to use a combination of these three functions. Today I’ll show you how you can combine these functions pretty comprehensively in all possible ways.

**Table of Contents**hide

## How to Use IF with INDEX & MATCH Functions in Excel: 3 Suitable Ways

Here we’ve got a data set with the **Names** of some students, and their **Marks** in** Physics** and **Chemistry** of a school called Sunflower Kindergarten.

Let’s try to combine the **IF **function, **INDEX **function, and **MATCH **function in all possible ways from this data set.

### 1. Wrap INDEX-MATCH Within IF Function in Excel

You can wrap an **INDEX-MATCH **formula within an **IF **function if necessary somehow.

For example, let’s think for a moment that the school authority has decided to find out the student with the least number in Physics. But that is only if the least number in **Physics **is less than **40**. However, if it is not, then there is no need to find out the student and it will show **“No Student”**.

Here are the steps to do that.

**Steps:**

- Firstly, select
**Cell F7**. - Secondly, insert the following formula and press
**Enter**.

`=IF(MIN(C5:C11)<40,INDEX(B5:D11,MATCH(MIN(C5:C11),C5:C11,0),1),"No Student")`

- After that, you will see that as the least number in Physics is less than
**40**(**20**in this case), we have found the student with the least number. That is**Alfred Moyes**.

**How Does the Formula Work?**

- In the beginning,
**MIN(C5:C11)**returns the smallest value in column**C5:C11**(**Marks in Physics**). In this example, it is**20**. See the**MIN function**for details. - So, the formula becomes
**IF(20<40,INDEX(B5:D11,MATCH(20,C5:C11,0),1),”No Student”).** - As the condition within the
**IF function**(**20<40**) is**TRUE**, it returns the first argument,**INDEX(B5:D11,MATCH(20,C5:C11,0),1).** - Then,
**MATCH(20,C5:C11,0)**searches for an exact match of**20**in column**C5:C11 (Marks in Physics)**and finds one in the**4th**row (In cell**C8**). So it returns**4**. - Now, the formula becomes
**INDEX(B5:D11,4,1).**It returns the value from the**4th**row and**1st**column of the range**B5:D11**(Data set excluding the**Column Headers**). - That is the name of the student with the least number in
**Physics**. And it is Alfred Moyes.

Now, if you understand this formula, can you tell me the formula to find out the student with the highest number in **Chemistry**? That is only if the highest number is **greater than **or **equal **to **80**. If not, return **“No student”**.

- Then, insert the following formula in
**Cell F7**.

`=IF(MAX(D5:D11)>=80,INDEX(B5:D11,MATCH(MAX(D5:D11),D5:D11,0),1),"No Student")`

- Finally, as the highest mark in Chemistry is greater than
**80**(**95**in this example), we have got the student with the highest marks in Chemistry. Ironically, it’s again**Alfred Moyes**.

**Read More:** **Excel INDEX MATCH to Return Multiple Values in One Cell**

### 2. Use IF Function within INDEX Functions in Excel

We can also use an **IF **function within the **INDEX **function if necessary somewhere.

Look at the following image. This time we have the examination record (Only **Physics**) of students of two different grades of Sunflower Kindergarten.

Now, we have **Cell B19** in the worksheet that contains **VII**. We want to derive a formula that will show the student with the highest marks of **Grade VII** in the adjacent cell if **B19** contains **VII**. However, if it contains **VIII**, the formula will show the student with the highest marks from **Grade VIII**.

Here are the steps to do that.

**Steps:**

- In the beginning, select
**Cell C19**. - After that, insert the following formula and press
**Enter**.

`=INDEX(IF(B19="VII",B6:C15,E6:F15),IF(B19="VII",MATCH(MAX(C6:C15),C6:C15,1),MATCH(MAX(F6:F15),F6:F15,1)),1)`

- Now, as there is
**VII**in**Cell B19**, we are getting the student with the highest marks from**Grade VII**. That is Steve Smith, with**98**marks.

- However, if we enter
**VIII**there, we will get the student with the highest marks from**Grade VIII**. That will be Paul Kennington.

**How Does the Formula Work?**

- To start with,
**IF(B19=”VII”,B6:C15,E6:F15)**returns**B6:C15**if cell**B19**contains**“VII”**. Otherwise, it returns**E6:F15.** - Similarly,
**IF(B19=”VII”,MATCH(MAX(C6:C15),C6:C15,1),MATCH(MAX(F6:F15),F6:F15,1))**returns**MATCH(MAX(C6:C15),C6:C15,1)**if**B19**contains**“VII”**. Otherwise, it returns**MATCH(MAX(F6:F15),F6:F15,1).** - Therefore, when
**B19**contains**“VII”**, the formula becomes**INDEX(B6:C15,MATCH(MAX(C6:C15),C6:C15,1),1).** - After that,
**MAX(C6:C15)**returns the highest marks from the range**C6:C15**(**Marks**of**Grade VII**). It is**98**here. See the**MAX function**for details. - So, the formula becomes
**INDEX(B6:C15,MATCH(98,C6:C15,1),1).** - Then,
**MATCH(98,C6:C15,1)**searches for an exact match of**98**in column**C6:C15.**It finds one in the**8th**row, in cell**C13**. So it returns**8**. - Finally, the formula now becomes
**INDEX(B6:C15,8,1).**It returns the value from the**8th**row and**1st**column of the data set**B6:C15.** - This is the student with the highest marks in
**Grade VII**, Steve Smith.

**Similar Readings**

**How to Use INDEX and MATCH with Multiple Criteria****INDEX-MATCH Formula to Find Minimum Value in Excel****INDEX, MATCH and MAX with Multiple Criteria in Excel****INDEX MATCH Multiple Criteria in Rows and Columns in Excel****INDEX Function to Match & Return Multiple Values Vertically in Excel**

### 3. Apply IF Function within MATCH Function in Excel

You can also use the **IF **function within the **MATCH **function if necessary.

Let’s go back to our original data set, with the **Marks** of **Physics** and **Chemistry** of the students of Sunflower Kindergarten. Now, we will perform another different task.

In **Cell B18** of the worksheet, there is the name of the subject **“Physics”**. We will derive a formula that will show the student with the highest marks in **Physics** in the adjacent Cell if **B18 **has **“Physics” **in it.

On the other hand, if it has** “Chemistry”**, it will show the student with the highest marks in **Chemistry**.

Follow the steps given below to do that.

**Steps:**

- Firstly, select
**Cell C18**. - Secondly, insert the following formula and press
**Enter**.

`=INDEX(B5:D14,MATCH(IF(B18="Physics",MAX(C5:C14),MAX(D5:D14)),IF(B18="Physics",C5:C14,D5:D14),0),1)`

- Now, it will show Steve Smith, because he is the highest marks getter in
**Physics**, and the**Cell B18**contains**“Physics”**.

- However, if we change cell
**F7**to**“Chemistry”**, it will show Alfred Moyes, the highest marks getter in**Chemistry**.

**How Does the Formula Work?**

- In the beginning,
**IF(B18=”Physics”,MAX(C5:C14),MAX(D5:D14))**returns**MAX(C5:C14)**if**F7**contains**“Physics”**. Otherwise, it returns**MAX(D5:D14).** - Similarly,
**IF(B18=”Physics”,C5:C14,D5:D14)**returns**C5:C14**if**B18**contains**“Physics”**. Otherwise, it returns**D5:D14.** - So, if
**B18**contains**“Physics”**, the formula becomes**INDEX(B5:D14,MATCH(MAX(C5:C14),C5:C14,0),1).** - Then,
**MAX(C5:C14)**returns the highest marks from the range**C5:C14**(**Marks**of**Physics**). It is**98**here. See the**MAX function**for details. - So, the formula becomes
**INDEX(B5:D14,MATCH(98,C5:C14,1),1).** - After that,
**MATCH(98,C5:C14,1)**searches for an exact match of**98**in column**C5:C14.**It finds one in the**8th**row, in cell**C12**. So, it returns**8**. - Lastly, the formula now becomes
**INDEX(B5:D14,8,1).**It returns the value from the**8th**row and**1st**column of the data set**B5:D14.** - Finally, this is the student with the highest marks in
**Physics**, Steve Smith.

**Similar Readings**

**Excel INDEX MATCH Example****Excel INDEX MATCH with Multiple Criteria and Multiple Results****INDEX MATCH with 3 Criteria in Excel****How to Match Multiple Criteria from Different Arrays in Excel****Use INDEX MATCH for Multiple Criteria Without Array****How to Use INDIRECT, INDEX, and MATCH Functions in Excel****INDEX MATCH Formula with Multiple Criteria in Different Sheet****INDEX MATCH across Multiple Sheets in Excel**

## Things to Remember

- Always set the
**3rd**argument of the**MATCH**function to**0**if you want an exact match. We hardly set it to anything else. - There are a few alternatives to the
**INDEX-MATCH**formula, like the**FILTER function**, the**VLOOKUP function**, the**XLOOKUP function****,**etc. - Among the alternatives, the
**FILTER**function is the best as it returns all the values that match the criteria. But it’s available in**Office 365**only.

## Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

**Download Practice Workbook**

You can download the workbook to practice yourself.

## Conclusion

Using these methods, you can use the **IF **function with the **INDEX-MATCH **function in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us. Please let us know if there are any more alternatives that we may have missed. Thank you!

**Related Readings**

**Excel INDEX-MATCH Formula to Return Multiple Values Horizontally****INDEX MATCH Multiple Criteria with Wildcard in Excel****[Fixed!] INDEX MATCH Not Returning Correct Value in Excel****How to Use INDEX MATCH Instead of VLOOKUP in Excel****INDEX MATCH with Multiple Matches in Excel****XLOOKUP vs INDEX-MATCH in Excel****How to Use Excel VBA INDEX MATCH with Array****VBA INDEX MATCH Based on Multiple Criteria in Excel****Excel INDEX MATCH If Cell Contains Text****Sum with INDEX-MATCH Functions under Multiple Criteria in Excel****SUMIF with INDEX and MATCH Functions in Excel****How to Sum Multiple Rows Using INDEX MATCH Formula****SUMPRODUCT with INDEX and MATCH Functions in Excel****INDEX-MATCH with Duplicate Values in Excel****How to Use INDEX-MATCH Function for Multiple Results in Excel****How to Use INDEX MATCH with Multiple Criteria for Date Range****INDEX, MATCH, and COUNTIF Functions with Multiple Criteria**