Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Apply Excel IF Statement with Multiple Conditions in Range

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.

Employee Record of Mars Group in Excel

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

How Does IF Statement Work in Excel in Any Range

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

Apply IF Statement with Multiple OR Type Conditions (Single Value)

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

Apply IF Statement with Multiple OR Type Conditions (Multiple Values)

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

Apply IF Statement with AND Type Conditions (Single Value)

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

Apply IF Statement with Multiple AND Type Conditions (Range of Values)

  • 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


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

Use Nested IF Statements to Match Multiple Conditions in Excel

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

Use Excel IF Statement with 3 Conditions with a Text Criteria

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

Combine IF and VLOOKUP Functions to Match Multiple Conditions in a Range

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


Further Readings

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