Many a time while working in Excel, we have to deal with the Excel IF between multiple ranges. Today I’ll show you can work with the IF function between multiple ranges in Excel.
How to Use Excel IF Between Multiple Ranges: 3 Easy Approaches
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.
Today our objective is to use the IF function of Excel between multiple ranges of this data set.
Method 1: Combining IF and OR Functions in Excel for OR Type Criteria Between Multiple Ranges
You can use a combination of the IF function and the OR function of Excel to handle OR type criteria between multiple ranges.
For example, let’s try to decide for each of the students, whether he/she has failed the examination or not.
And the criteria for failing is simple. You fail if you fail in at least one subject (Get marks less than 40). Therefore, it’s an OR-type condition between multiple ranges.
Steps:
- Firstly, select the E5 cell and enter this formula:
=IF(OR(C5<40,D5<40),"Fail","Pass")
- Then drag the Fill Handle to copy this formula to the rest of the cells.
- Finally, you can see the students who have failed in at least one subject have been judged as Fail, and they marked yellow in the data set.
- C4<40 returns TRUE if the mark in cell C4 (Mark in Physics) is less than 40, otherwise returns FALSE. Same for D4<40.
- OR(C4<40,D4<40) returns TRUE if at least one cell between C4 and D4 contains less than 40, otherwise returns FALSE.
- Finally, IF(OR(C4<40,D4<40),”Fail”,”Pass”) returns “Fail” if it encounters a TRUE. Otherwise returns “Pass”.
Read More: How to Use Excel IF Function with Range of Values
Method 2: Nesting IF and AND Functions in Excel for AND Type Criteria Between Multiple Ranges
You can combine the IF function and the AND function of Excel to handle AND type criteria between multiple ranges.
For example, let’s try to decide for each of the students this time, whether he/she has passed the examination or not.
And the criteria for failing is that you pass if you pass in all the subjects (Get marks greater than or equal to 40), otherwise not.
Steps:
- Therefore, it’s an AND-type condition between multiple ranges.
- So, choose the E5 cell and enter this formula:
=IF(AND(C5>=40,D5>=40),"Pass","Fail")
- Then, drag the Fill Handle to copy this formula to the rest of the cells.
- Finally, you can see the students who have passed both subjects have been judged as Pass, and they marked yellow in the data set.
- C4>=40 returns TRUE if the mark in cell C4 (Mark in Physics) is greater than or equal to 40, otherwise returns FALSE. Same for D4>=40.
- AND(C4>=40,D4>=40) returns TRUE if at least one cell between C4 and D4 contains greater than or equal to 40, otherwise returns FALSE.
- Finally, IF(AND(C4>=40,D4>=40),”Pass”,”Fail”) returns “Pass” if it encounters a TRUE. Otherwise returns “Fail”.
Method 3: Using Nested IF Function for AND Type Criteria Between Multiple Ranges
You can use the nested IF function to handle AND Type criteria between multiple ranges in Excel.
Let’s repeat the same example. Take a decision for each student whether he/she has passed the examination or not.
This time we will accomplish this using the nested IF function.
Steps:
- Firstly, select the E5 cell and enter this formula:
=IF(C5>=40,IF(D5>=40,"Pass","Fail"),"Fail")
- Then, drag the Fill Handle to copy this formula to the rest of the cells.
- As a result, we have again judged all the students who passed in both subjects as Pass, marked yellow in the data set.
- If C4>=40 is TRUE, the formula enters into IF(D4>=40,”Pass”,”Fail”), otherwise returns “Fail”.
- Then if D4>=40 is also TRUE, it returns “Pass”, otherwise it returns “Fail”.
- Thus it returns “Pass” only if one passes in both subjects, otherwise, it returns “Fail”.
Using Excel IFS Function Instead of IF for AND Type Criteria Between Multiple Ranges
Finally, we will use the IFS function of Excel to deal with OR type multiple criteria instead of the IF function.
We will accomplish the task in Method 1 here and will decide for each student whether he/she has failed or not.
Steps:
- Firstly, choose the E5 cell and enter this formula:
=IFS(C5<40,"Fail",D5<40,"Fail",TRUE,"Pass")
- Then drag the Fill Handle to copy this formula to the rest of the cells.
- Finally, you can see that we have again termed the students who failed in at least one subject as “Fail”, marked yellow in the data set.
- The IFS function returns the corresponding value with the first TRUE argument, otherwise, it returns a N/A
- If C4<40, it returns “Fail”. If not, then it checkers whether D4<40 or not. If then, it returns “Fail”.
- If D4<40 is also FALSE, then it encounters the next TRUE and returns “Pass”.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
Using these methods, we can use the IF function of Excel between multiple ranges. Do you have any questions? Feel free to ask us.
Related Articles
- How to Use Multiple IF Statements with Text in Excel
- How to Use IF Function with Multiple Conditions in Excel
- How to Write Greater Than or Equal To in Excel IF Function
- How to Use MAX IF Function in Excel
- If a Value Lies Between Two Numbers Then Return Result in Excel
- How to Check If a Value Is Between Two Numbers in Excel
- How to Make Yes 1 and No 0 in Excel
- How to Check If Value Exists in Range in Excel
- [Fixed!] IF Function Is Not Working in Excel
