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.

**Table of Contents**hide

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

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.

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.

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

**How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)****How to use SUMPRODUCT IF in Excel****Excel VBA: Combined If and Or (3 Examples)****How to Use Stock Ageing Analysis Formula in Excel (2 Easy Ways)****Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)****Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)****How to Use IF Formula for Aging Buckets in Excel (3 Suitable Examples)**