Excel IF between Multiple Ranges (4 Approaches)

Excel IF between Multiple Ranges (4 Approaches)

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.


Download Practice Workbook


4 Approaches to Use Excel IF between Multiple Ranges

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 Excel IF between Multiple Ranges

Today our objective is to use the IF function of Excel between multiple ranges of this data set.


1. Use IF and OR Functions of 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 an OR type criteria between multiple ranges.

For example, let’s try to decide for each of the students, whether he/she has failed in 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.

Select the first cell of a new column and enter this formula:

=IF(OR(C4<40,D4<40),"Fail","Pass")

Then drag the Fill Handle to copy this formula to the rest of the cells.

See, the students who have failed in at least one subject have been judged as Fail, and they marked yellow in the data set.

Explanation of the Formula:

  • 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 Multiple If Conditions in Excel for Aging (5 Methods)


2. Combine IF and AND Functions of 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.

Therefore, it’s an AND type condition between multiple ranges.

Select the first cell of a new column and enter this formula:

=IF(AND(C4>=40,D4>=40),"Pass","Fail")

Then drag the Fill Handle to copy this formula to the rest of the cells.

OR Function to Use IF between Multiple Ranges in Excel

See, the students who have passed in both the subjects have been judged as Pass, and they marked yellow in the data set.

Explanation of the Formula:

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

Read More: How to Write Greater Than or Equal To in Excel IF Function


3. Use 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 in the examination or not.

This time we will accomplish this using the nested IF function.

Select the first cell of a new column and enter this formula:

=IF(C4>=40,IF(D4>=40,"Pass","Fail"),"Fail")

Then drag the Fill Handle to copy this formula to the rest of the cells.

We have again judged all the students who passed in both the subjects as Pass, marked yellow in the data set.

Explanation of the Formula:

  • 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 the subjects, otherwise it returns “Fail”.

Read More: How to Use MAX IF Function in Excel


4. Use IFS Function of Excel 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, will decide for each student whether he/she has failed or not.

Select the first cell of a new column and enter this formula:

=IFS(C4<40,"Fail",D4<40,"Fail",TRUE,"Pass")

Then drag the Fill Handle to copy this formula to the rest of the cells.

IFS Function of Excel instead of IF between Multiple Ranges of OR Type

See, we have again termed the students who failed in at least one subject as “Fail”, marked yellow in the data set.

Explanation of the Formula:

  • The IFS function returns the corresponding value with the first TRUE argument, otherwise, it returns a N/A error.
  • 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”.

Related Content: How to Use Excel IF Function with Range of Values


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

ExcelDemy
Logo