IF with INDEX-MATCH in Excel (3 Suitable Approaches)

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.


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.

Data Set to Use IF with INDEX MATCH in Excel

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")

IF with INDEX-MATCH Function in Excel

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.

New Data Set to Use IF with INDEX MATCH in Excel

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)

IF within INDEX Function to Use IF with INDEX MATCH in Excel

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.

IF within INDEX Function to Use IF with INDEX MATCH in Excel

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)

IF Function Inside MATCH Function to Use IF with INDEX MATCH in Excel

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 Function Inside MATCH Function to Use IF with INDEX MATCH in Excel

  • 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


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

XLOOKUP vs INDEX-MATCH in Excel (All Possible Comparisons)

Index Match Multiple Criteria in Rows and Columns in Excel

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