How to Use Excel IF Between Multiple Ranges (3 Easy Ways)

Last updated: September 25, 2023
Get FREE Advanced Excel Exercises with Solutions!

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.

Easy Approaches to Use Excel IF between Multiple Ranges

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

Combining IF and OR Functions of Excel for OR Type Criteria between Multiple Ranges

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

Showing Results for Combining IF and OR Functions of Excel for OR Type Criteria between Multiple Ranges

Formula Breakdown
  • 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")

Nesting IF and AND Functions of Excel for AND Type Criteria between Multiple Ranges

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

Showing Results

Formula Breakdown
  • 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")

Using Nested IF Function for AND Type Criteria between Multiple Ranges

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

Showing Results

Formula Breakdown
  • 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")

Using Excel IFS Function instead of IF for AND Type Criteria between Multiple Ranges

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

Showing Results

Formula Breakdown
  • 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

Rifat Hassan
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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo