Today I will be showing how you can apply the Excel **IF statement with multiple conditions** in any range.

**Download Practice Workbook**

You can download the following practice workbook for your exercise while reading this article.

## How Does IF Statement in Excel Work in Any Range for Specified Conditions?

Before going to the main discussion, let us introduce you to today’s data set. We have the employee record of a company named **Mars Group**.

We have the **Employee Names**, their **Starting Dates**, and **Salaries** in columns **B**, **C** and **D** respectively.

Now think for a moment, the chief of **Mars Group** wants to come to a decision. Which is- if the average salary of his employees is less than **$25000**, he will increase the salary of each employee by **$5000**.

But the question is, how can he come to the decision?

The** IF** **function** of Excel will come in handy here. Just write this formula in any cell of your worksheet and see the result:

`=IF(AVERAGE(D5:D20)<25000,"Increase","Do not increase")`

See, Excel’s **IF** function has decided it for you. It has first determined whether the average salary is less than **$25000** or not. When it saw the average salary is not less than **$25000**, it advised you not to increase the salary.

So we can see that the **IF** function takes three arguments:

- One criterion
- One output is displayed if the criterion is satisfied
- One output is displayed if the criterion is not satisfied (Optional. Default is
**“FALSE”**)

So in short, the **IF** function takes one criterion and two outputs. It returns the first output if the criterion is satisfied, and returns the second if the criterion is not satisfied.

And the syntax is:

**=IF(logical_test,value_if_true,[value_if_false])**

Now I hope you have understood how the** IF** function of Excel works with one condition.

**5 Examples of Applying Excel IF Statement with Multiple Conditions in Any Range**

Now, let’s try to work with multiple conditions using the **IF** statement in Excel for a given range. We will see 5 relevant examples in this part.

**1. Apply IF Statement with Multiple OR Type Conditions**

**i. Conditions for a Single Value**

Let’s think for a moment, the chief of **Mars Group** has brought a bit of flexibility in his decision.

He will increase the salary of each employee if the average salary is less than **$25000**, or the lowest salary of the employees is less than **$20000**.

**⊕ Workaround:**

- We see, there are two conditions here. But these are OR-type conditions. That means, the condition is satisfied if either one or both conditions are satisfied.
- Using an
**IF**statement for this type of multiple conditions is quite easy. Just wrap the two conditions within an**OR function**of Excel. - The formula that we shall use here is:

`=IF(OR(AVERAGE(D5:D20)<25000,MIN(D5:D20)<20000),"Increase","Do not increase")`

- See, this time Excel has recommended we increase the salary.

**Formula Explanation:**

Let’s break down the formula here.

**OR(AVERAGE(D5:D20)<25000,MIN(D5:D20)<20000)**

It returns **TRUE** if any one or both of the criteria are satisfied. Otherwise, it returns **FALSE.** In this case, **OR(AVERAGE(D5:D20)**<25000,MIN(D5:D20)<20000has returned **TRUE** because the average salary is not less than **$25000**, but the lowest salary is less than **$20000**.**Result:** TRUE

- So the formula becomes:
*=IF(TRUE,”Increase”,”Do not increase”)*

As it is **TRUE** inside the **IF** function, it returns the first output, “**Increase**”.**Result:** “Increase”

- Now, if you understand this, can you tell me what the formula will be to get a “
**Yes**” if either the highest salary is greater than**$40000**or the lowest salary is less than**$20000**, otherwise “**No**”?

Yes. You are right. The formula will be:

`=IF(OR(MAX(D5:D20)>40000,MIN(D5:D20)<20000),"Yes","No")`

**ii. Conditions for a Range of Values**

Now consider a different scenario.

The chief of **Mars Group** has decided he will increase the salary of those employees whose present salaries are less than **$25000**, or who started their jobs before **1/1/2015**.

But how can he identify those employees?

**⊕ Workaround:**

- In place of using just a single cell reference within the
**IF**function, you can use a range of cell references inside the function. - See the below formula.

`=IF(OR($D5<25000,$C5<DATE(2015,1,1)),$B5,"")`

- Here, I have inserted the formula in the first cell of the new column,
**Cell F4**. - And then dragged the
**Fill Handle**through the rest of the cells. - It returned the names of all the employees whose salaries are less than
**$25000**, or who started their jobs before**January 01, 2015**. - If you carefully notice, you will find that in lieu of inserting a single cell reference within the
**IF**function, I have inserted a range of cell references (**$D$4:$D$19**) within the function.

Of course, you can do that. And it will check the criteria one by one for each cell of the range.

**Formula Explanation:**

For a better understanding, let’s break down the formula.

**OR($D5<25000,$C5<DATE(2015,1,1))**

It checks **Cell** **D5** and **Cell** **C5**** **and returns a **TRUE** if either salary is less than **$25000** or starting date is less than **January 01, 2015**.**Result:** TRUE.

- So the formula becomes:
**=IF(TRUE,B5,””)**

For **TRUE** in the criteria range, it returns the content of the corresponding cell of **Column** **B**, which means the employee name, and for each **FALSE**, it returns a blank cell. We have used **Absolute Cell Reference** here because we do not want to change the cell references when we drag the** Fill Handle**.**Result: **“Steve Smith”.

**Note:**

You may want to **remove the blank cells from the list**, which means that you may just want a list of the employees whose salaries are to be increased. Unfortunately, you can’t do this using only the **IF** function. But obviously, there are ways. One way to accomplish this is by using the **FILTER function** of Excel.

**Read More:** **Excel VBA: Combined If and Or (3 Examples)**

**2. Apply IF Statement with Multiple AND Type Conditions**

We can also use the Excel **IF** statement for multiple **AND** conditions in any range.

**i. Conditions for a Single Value**

If you understand the earlier section well, can you give an answer to another question?

What will be the formula if the chief of the company wants to increase the salary of each employee if the average salary is less than **$25000** and the lowest salary is **$20000**?

**⊕ Workaround:**

- Just wrap the two conditions within an
**AND function**in place of the**OR**function. - Just like this:

`=IF(AND(AVERAGE(D5:D20)<25000,MIN(D5:D20)<20000),"Increase","Do not increase")`

- See, this time Excel has suggested we not increase the salary because both the conditions, average salary less than
**$25000**and lowest salary less than**$20000**, are not satisfied. Only one condition is satisfied. - If you want to know more details about the formula,
**go to section i of Example 1**.

**ii. Conditions for a Range of Values**

The Chief of **Mars Group** is, in fact, a very confused man. This time he has taken another decision.

He will increase the salaries of only those employees who have a present salary of less than **$20000** and started the job before **January 01, 2017**.

How can he find those employees?

**⊕ Workaround:**

- Yes. You are right. Just use the formula of section
**1.2**with an**AND**function instead of an**OR**function.

`=IF(AND($D5<25000,$C5<DATE(2017,1,1)),$B5,"")`

- See, we have got employees who satisfy both conditions.

To know details about the formula, **go to section ii of Example 1**.

**Read More:** **Excel VBA: Combining If with And for Multiple Conditions**

**Similar Readings**

**How to Use PERCENTILE with Multiple IF Condition in Excel (3 Examples)****Excel IF between Multiple Ranges (4 Approaches)****How to Use Multiple IF Condition in Excel (3 Examples)**

**3. Use Nested IF Statements to Apply Multiple Conditions Consecutively**

In the previous section, we determined whether both the conditions, average salary less than **$25000**, and lowest salary less than **$20000** are satisfied or not.

But have you ever thought that we can determine this in another way- by using an **IF** within another **IF** function?

**⊕ Workaround:**

- We shall first check whether the lowest salary is less than
**$20000**or not. - If not, it will return
**“Do not increase”**. - But if it is, then we will again check whether the average salary is less than
**$25000**or not. - If not, then it will return
**“Do not increase”**. - But if it is, this time it will return
**“increase”** - So the complete formula will be:

`=IF(MIN(D5:D20)<20000,(IF(AVERAGE(D5:D20)<25000,"Increase","Do not increase")),"Do not increase")`

- See, Excel has suggested we not increase the salary, because both conditions are not satisfied.

**Formula Explanation:**

Let’s break down the formula for better understanding.

`MIN(D5:D20)<20000`

It returns **TRUE** if the lowest salary is less than **$20000.** Otherwise, it returns **FALSE**. Here it returns **TRUE**.**Result: **TRUE.

- So the formula becomes:
**=IF(TRUE,(IF(AVERAGE(D5:D20)<25000,”Increase”,”Do not increase”)),”Do not increase”)**

As **IF** sees a **TRUE**, it enters into the first output. That means it enters into **(IF(AVERAGE(D5:D20)<25000,”Increase”,”Do not increase”))**

**AVERAGE(D5:D20)<25000**

It returns a **TRUE** if the average salary is less than **$25000**, otherwise, it returns **FALSE**. This time it returns **FALSE**.**Result: **FALSE.

- So the formula becomes:
**=IF(TRUE,(IF(FALSE,”Increase”,”Do not increase”)),”Do not increase”)**

So it returns the second output of the second **IF**, **“Do not increase”**.**Result:** “Do not increase”.

- Now if you understand this, let’s try to answer an old question differently.

Can you tell me what the formula will be to get a “**Yes**” if either the highest salary is greater than **$40000** or the lowest salary is less than **$20000**, otherwise “**No**”?

- Yes, you are right. The formula will be:

`=IF(MAX(D5:D20)>40000,"Yes",(IF(MIN(D5:D20)<20000,"Yes","No")))`

**Read More:** **VBA IF Statement with Multiple Conditions in Excel (8 Methods)**

**4. Use Excel IF Statement with 3 Conditions Including a Text Criteria**

Let’s think again. The chief of **Mars Group** wants to add one more condition based on the text. For that purpose, he added the gender of the employees to the dataset. Now, he wants to know the name of the employee whose salary is below **$25000**, who joined after **01/01/2017**, and male.

**⊕ Workaround:**

- This time, we need to use the following formula based of the
**AND**function with the**IF**statement.

`=IF(AND($E5<25000,$C5<DATE(2017,1,1),$D5="Male"),$B5,"")`

- Excel returned the name of the employees.

**Formula Explanation:**

Let’s break down the formula for better understanding.

*E5<25000*

It checks whether **E5** is lower than **25000** or not.**Result:** FALSE

*C5<DATE(2017,1,1)*

It checks whether **C5** is earlier than the given date with the **DATE** function.**Result:** TRUE

*D5=”Male”*

It checks whether **D5** matches the given text or not.**Result:** TRUE

**AND($E5<25000,$C5<DATE(2017,1,1),$D5=”Male”)**

This applies **AND** operation with the given three conditions.**Result:** FALSE

**=IF(AND($E5<25000,$C5<DATE(2017,1,1),$D5=”Male”),$B5,””)**

This returns based on the result of the **AND** function.**Result: **(blank)

**Read More: ****Excel IF Function with 3 Conditions (5 Logical Tests)**

**5. Combine IF with VLOOKUP Function to Match Multiple Criteria and Lookup the Value**

In this section, we will perform the same operation of the last method with the help of the **VLOOKUP function**.

**⊕ Workaround:**

- We will just change the starting date to
**01/01/2015**. - Look at the following formula used on
**Cell H7**.

`=VLOOKUP(H4,IF((D5:D20<=H6)*(E5:E20<=H5),B5:E20,""),2,FALSE)`

- We get the name of the employee who started working on or before
**01/01/2015**, whose salary is equal to or lower than**$25000**, and male.

**Formula Explanation:**

*D5:D20<=H6*

It checks whether the given range is equal to or lower than **H6.**

**Result:**{TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE}

*E5:E20<=H5*

It checks whether the given range is equal to or lower than **H5**.**Result:** {FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE}

*(D5:D20<=H6)*(E5:E20<=H5)*

This multiplies the results get from the previous two operations.**Result:** {0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0}

**IF((D5:D20<=H6)*(E5:E20<=H5),B5:E20,””)**

Fulfilling the results of the given two conditions, we apply the **IF function**.**Result: **[Male, Kane Austin, 03/06/2014, 25000]

*VLOOKUP(H4,IF((D5:D20<=H6)*(E5:E20<=H5),B5:E20,””),2,FALSE)*

Here, the **VLOOKUP** returns the **2nd** component of this newly formed table.**Result: **Kane Austin

**Read More:** **Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)**

**Conclusion**

Using these methods, you can use any **IF** statement with multiple conditions in a range of both **AND** types and **OR **types in Excel. Do you know any other method? Or do you have any questions? Feel free to inform us. Visit **ExcelDemy** for more articles like this.