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
Download Practice Workbook
1. Wrap INDEX-MATCH Within IF Function in Excel
2. Use IF Function within INDEX Functions in Excel
3. Apply IF Function within MATCH Function in Excel
Things to Remember
Practice Section
Conclusion
Related Readings
Download Practice Workbook
You can download the workbook to practice yourself.
3 Suitable Ways to Use IF with INDEX & MATCH Functions 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 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
- 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
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.
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.
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. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!
Related Readings
- Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
- INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
- How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)
- Index Match with Multiple Matches in Excel (5 Methods)
- How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results
FAQs
Can you do INDEX match with 3 criteria? ›
To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.
Can you use INDEX match with multiple criteria? ›It searches the row position of a value/text in one column (using the MATCH function) and returns the value/text in the same row position from another column to the left or right (using the INDEX function). One of the advantages of using Index Match is that you can search for a value that matches multiple criteria.
What does index match do if there are multiple matches? ›INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches. In other words, you can look up and return values even if there are no unique values to look for.
How do you write multiple IF statements in Excel? ›How do you put 2 conditions in an Excel IF function? To put two conditions in an IF formula in Excel, you can use the AND or OR function along with the IF function. For example, =IF(AND(A1>50, B1>60), “Pass”, “Fail”) will check if the value in cell A1 is greater than 50 and the value in cell B1 is greater than 60.
Can you use an if statement with INDEX-match? ›In Index -Match combination , if may be used to show defined text/value in case of non availability of lookup value in list.
How do I check for multiple conditions in Excel? ›Use the IF function along with AND, OR and NOT to perform multiple evaluations if conditions are True or False. The condition you want to test. The value that you want returned if the result of logical_test is TRUE. The value that you want returned if the result of logical_test is FALSE.
How do I compare three columns in Excel using if condition? ›- Compare 3 Columns for Matches.xlsx.
- =IF(AND(C5=D5,D5=E5),"Yes","No")
- =AND($C5=$D5,$D5=$E5)
- =IF(COUNTIF(C5:D5,C5)+COUNTIF(D5:E5,E5)=2,"No","Yes")
You may use VLOOKUP when the data is relatively small and the columns will not be inserted/deleted. But in other cases, it is best to use a combination of INDEX and MATCH functions. You use the following syntax using INDEX and MATCH together: =INDEX(range, MATCH(lookup_value, lookup_range, match_type)) .
Can you combine sum and index match? ›Use of SUMIFS with INDEX & MATCH Functions in Excel
If you use the SUMIFS function with the INDEX and MATCH functions inside, you can add more than one criterion, which you can't do by just using the SUMIF function. To do this, ensure you input your Sum Range, then Criteria Range, then Range Criteria.
No built-in error handling: Unlike XLOOKUP, the INDEX and MATCH combination does not have a built-in error handling feature, so the formula will simply return #N/A when a lookup fails. To provide a more friendly or helpful message, another function like IFERROR or IFNA needs to be included (example).
Can XLOOKUP use multiple criteria? ›
XLOOKUP is especially preferred because of its ability to look up multiple criteria at once. The best way to do that is to use XLOOKUP with multiple criteria to use the Boolean logic.