In this article, I’ll show you** how you can use IF with INDEX-MATCH in Excel. **The** IF function, INDEX function, **and the** 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

**Download Practice Workbook**

**3 Approaches to Use IF with INDEX-MATCH in Excel**

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 an IF Function to Use IF with INDEX-MATCH 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.

If it is not, then there is no need to find out the student and it will show **“No Student”**.

**⧪ How can the school authority accomplish this?**

Easy. They can wrap the **INDEX-MATCH** formula within an **IF function** like this formula:

`=IF(MIN(C4:C13)<40,INDEX(B4:D13,MATCH(MIN(C4:C13),C4:C13,0),1),"No Student")`

See, 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.

**⧪**** Explanation of the Formula:**

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

**⧪**** More Task To Complete:**

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

Yes. You have guessed right. The formula will be:

`=IF(MAX(D4:D13)>=80,INDEX(B4:D13,MATCH(MAX(D4:D13),D4:D13,0),1),"No Student")`

See, as the highest marks 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.

**2. Use IF Function within the INDEX Function to Use IF with INDEX-MATCH 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 a cell **H9** 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 **H9** contains **VII**.

And if it contains **VIII**, the formula will show the student with the highest marks from **Grade VIII**.

**⧪ How to execute this?**

You can insert an **IF function** inside an** INDEX function** to accomplish the task. The formula will be:

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

Look, as there is **VII** in cell **H9**, we are getting the student with the highest marks from **Grade VII**.

That is Steve Smith, with marks 98.

And if we enter **VIII** there, we will get the student with the highest marks from **Grade VIII**.

That will be Paul Kennington.

**⧪**** Explanation of the Formula:**

**IF(H9=”VII”,B6:C15,E6:F15)**returns**B6:C15**if cell**H9**contains**“VII”**. Otherwise, it returns**E6:F15.**- Similarly,
**IF(H9=”VII”,MATCH(MAX(C6:C15),C6:C15,1),MATCH(MAX(F6:F15),F6:F15,1))**returns**MATCH(MAX(C6:C15),C6:C15,1)**if**H9**contains**“VII”**. Otherwise, it returns**MATCH(MAX(F6:F15),F6:F15,1).** - Therefore, when
**H9**contains**“VII”**, the formula becomes**INDEX(B6:C15,MATCH(MAX(C6:C15),C6:C15,1),1).** **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).** **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**.- 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.

**3. Use IF Function within the MATCH Function to Use IF with INDEX-MATCH 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 **F4** 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 F4 has “Physics” in it.**

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

**⧪ How to execute this?**

Easy. Use an **IF function** inside the **MATCH function**, like this formula:

`=INDEX(B4:D13,MATCH(IF(F7="Physics",MAX(C4:C13),MAX(D4:D13)),IF(F7="Physics",C4:C13,D4:D13),0),1)`

It is showing Steve Smith, because he is the highest marks getter in **Physics**, and the cell **F7** contains **“Physics”**.

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

**⧪**** Explanation of the Formula:**

**IF(F7=”Physics”,MAX(C4:C13),MAX(D4:D13))**returns**MAX(C4:C13)**if**F7**contains**“Physics”**. Otherwise, it returns**MAX(D4:D13).**- Similarly,
**IF(F7=”Physics”,C4:C13,D4:D13)**returns**C4:C13**if**F7**contains**“Physics”**. Otherwise, it returns**D4:D13.** - So, if
**F7**contains**“Physics”**, the formula becomes**INDEX(B4:D13,MATCH(MAX(C4:C13),C4:C13,0),1).** **MAX(C4:C13)**returns the highest marks from the range**C4:C13**(**Marks**of**Physics**). It is**98**here. See the**MAX function**for details.- So, the formula becomes
**INDEX(B4:D13,MATCH(98,C4:C13,1),1).** **MATCH(98,C4:C13,1)**searches for an exact match of**98**in column**C4:C13.**It finds one in the**8th**row, in cell**C11**. So it returns**8**.- The formula now becomes
**INDEX(B4:D13,8,1).**It returns the value from the**8th**row and**1st**column of the data set**B4:D13.** - This is the student with the highest marks in
**Physics**, Steve Smith.

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

**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 we have any questions? Feel free to ask us.

**Related Readings**

**Excel INDEX and MATCH Functions with Multiple Criteria (4 Formulas)**

**INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)**

**INDEX, MATCH and MAX with Multiple Criteria in Excel**