Checking for Multiple Conditions in Excel Formula

Sometimes, you may need to perform different actions or calculations based on multiple criteria or conditions in Excel. For example, you may want to assign grades to students based on their test scores, calculate commissions for salespeople based on their sales volume, or filter data based on multiple criteria. In this article, you will learn how to use the IF function with multiple conditions in Excel, using the AND, OR, and NOT functions, as well as other alternatives such as the IFS and SWITCH functions.

The IF function is one of the most commonly used functions in Excel. It allows you to make a logical comparison between a value and what you expect, and return a result based on whether the condition is true or false. The basic syntax of the IF function is:

=IF (logical_test, value_if_true, value_if_false)

Where:

• logical_test is the condition that you want to test, such as A1>10, B2=“Yes”, or C3=D3.
• value_if_true is the value that you want to return if the logical_test is true, such as “Pass”, 100, or another formula.
• value_if_false is the value that you want to return if the logical_test is false, such as “Fail”, 0, or another formula.

For example, the formula =IF (A1>10, “High”, “Low”) will return “High” if the value in cell A1 is greater than 10, and “Low” if the value in cell A1 is less than or equal to 10.

However, the IF function can only test one condition at a time. If you need to test multiple conditions and return different results based on the combination of conditions, you need to use nested IF functions or other functions that can handle multiple criteria, such as AND, OR, NOT, IFS, and SWITCH.

Procedures

Using Nested IF Functions

One way to use the IF function with multiple conditions is to nest one IF function inside another, as shown below:

=IF (condition1, value_if_true1, IF (condition2, value_if_true2, value_if_false))

This formula tests the first condition; if true, it returns the first value. If the first condition is false, the formula moves to the second condition and returns the second value if it’s true. If the second condition is also false, the formula returns the final value. You can nest up to 64 IF functions in Excel, but it is not recommended to use more than a few, as the formula can become very complex and difficult to read and maintain.

For example, suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50. You can use the following nested IF function to check if a student passed or failed:

=IF (AND (B2>50, C2>50), “Pass”, “Fail”)

This formula uses the AND function to test if both conditions are true, and returns “Pass” if they are, and “Fail” if they are not. The AND function can take up to 255 conditions and returns TRUE only if all of them are true, and FALSE otherwise.

Alternatively, you can use the following nested IF function to assign letter grades to students based on their average score:

=IF (AVERAGE (B2:C2)>=90, “A”, IF (AVERAGE (B2:C2)>=80, “B”, IF (AVERAGE (B2:C2)>=70, “C”, IF (AVERAGE (B2:C2)>=60, “D”, “F”))))

This formula calculates the average score of the two tests in columns B and C, and assigns an “A” if the average is 90 or greater, a “B” if the average is between 80 and 89, a “C” if the average is between 70 and 79, a “D” if the average is between 60 and 69, and an “F” if the average is less than 60.

Using AND, OR, and NOT Functions

Another way to use the IF function with multiple conditions is to use the AND, OR, and NOT functions, which can test multiple criteria and return a logical value of TRUE or FALSE. The syntax of these functions are:

=AND (condition1, condition2, …) =OR (condition1, condition2, …) =NOT (condition)

Where:

• condition1, condition2, … are the conditions that you want to test, such as A1>10, B2=“Yes”, or C3=D3.
• condition is the condition that you want to reverse, such as A1>10, B2=“Yes”, or C3=D3.

The AND function returns TRUE if all the conditions are true, and FALSE otherwise. The OR function returns TRUE if any of the conditions is true, and FALSE otherwise. The NOT function returns the opposite of the condition, i.e., TRUE if the condition is false, and FALSE if the condition is true.

You can use these functions in the logical_test argument of the IF function to test multiple conditions and return different results based on the combination of conditions. For example, suppose you have a table showing the sales of some salespeople in a certain month. You can use the following IF function with the AND function to calculate the tiered commission for the salespeople based on their sales volume:

=IF (AND (B2>=40000, B2<50000), B20.14, IF (AND (B2>=30000, B2<40000), B20.12, IF (AND (B2>=20000, B2<30000), B20.105, IF (AND (B2>=10000, B2<20000), B20.08, 0))))

This formula tests if the sales volume in column B is between 40,000 and 50,000, and returns the sales volume multiplied by 14% if it is. If not, it tests if the sales volume is between 30,000 and 40,000, and returns the sales volume multiplied by 12% if it is. If not, it tests if the sales volume is between 20,000 and 30,000, and returns the sales volume multiplied by 10.5% if it is. If not, it tests if the sales volume is between 10,000 and 20,000, and returns the sales volume multiplied by 8% if it is. If none of the conditions is true, the formula returns 0.

You can also use the OR function to test if any of the conditions is true, and return a result accordingly. For example, suppose you have a table showing the type and color of some vehicles. You can use the following IF function with the OR function to check if the vehicle is either a car or red, and return “Yes” if it is, and “No” if it is not:

=IF (OR (A2=“Car”, B2=“Red”), “Yes”, “No”)

This formula tests if the value in column A is “Car” or the value in column B is “Red”, and returns “Yes” if either condition is true, and “No” if both conditions are false.

You can also use the NOT function to reverse the result of a condition, and return a result accordingly. For example, suppose you have a table showing the names and ages of some people. You can use the following IF function with the NOT function to check if the person is not older than 18, and return “Minor” if they are, and “Adult” if they are not:

=IF (NOT (B2>18), “Minor”, “Adult”)

This formula tests if the value in column B is not greater than 18, and returns “Minor” if it is true, and “Adult” if it is false.

You can also combine the AND, OR, and NOT functions to create more complex conditions. For example, suppose you have a table showing the names and genders of some people. You can use the following IF function with the AND and NOT functions to check if the person is a female and not named Alice, and return “Yes” if they are, and “No” if they are not:

=IF (AND (B2=“Female”, NOT (A2=“Alice”)), “Yes”, “No”)

This formula tests if the value in column B is “Female” and the value in column A is not “Alice”, and returns “Yes” if both conditions are true, and “No” if either condition is false.

Using IFS and SWITCH Functions

If you have Excel 2019 or later, or Excel for Microsoft 365, you can use the IFS and SWITCH functions as alternatives to nested IF functions or IF functions with AND, OR, and NOT functions. These functions can simplify your formulas and make them easier to read and maintain.

The IFS function can test multiple conditions and return a value corresponding to the first true condition. The syntax of the IFS function is:

=IFS (condition1, value1, [condition2, value2], …)

Where:

• condition1, condition2, … are the conditions that you want to test, such as A1>10, B2=“Yes”, or C3=D3.
• value1, value2, … are the values that you want to return if the corresponding condition is true, such as “Pass”, 100, or another formula.

For example, you can use the following IFS function to assign letter grades to students based on their average score:

=IFS (AVERAGE (B2:C2)>=90, “A”, AVERAGE (B2:C2)>=80, “B”, AVERAGE (B2:C2)>=70, “C”, AVERAGE (B2:C2)>=60, “D”, TRUE, “F”)

This formula is equivalent to the nested IF function shown earlier, but it is shorter and easier to understand. The IFS function tests each condition in order, and returns the corresponding value if the condition is true. If none of the conditions is true, the formula returns the last value, which is “F” in this case.

The SWITCH function can evaluate an expression and return a value based on a matching case. The syntax of the SWITCH function is:

=SWITCH (expression, case1, value1, [case2, value2], …, [default])

Where:

• expression is the value or formula that you want to evaluate, such as A1, B2+10, or C3=“Yes”.
• case1, case2, … are the values that you want to compare with the expression, such as 10, “Yes”, or another formula.
• value1, value2, … are the values that you want to return if the expression matches the corresponding case, such as “High”, 100, or another formula.
• default is the optional value that you want to return if the expression does not match any of the cases, such as “Low”, 0, or another formula.

For example, you can use the following SWITCH function to calculate the tiered commission for the salespeople based on their sales volume:

=SWITCH (B2, B2>=40000, B20.14, B2>=30000, B20.12, B2>=20000, B20.105, B2>=10000, B20.08, 0)

This formula is equivalent to the nested IF function shown earlier, but it is simpler and more concise. The SWITCH function evaluates the sales volume in column B, and returns the sales volume multiplied by the corresponding percentage if it matches one of the cases. If none of the cases is matched, the formula returns the last value, which is 0 in this case.

Explanation

To summarize, you can use the IF function with multiple conditions in Excel to perform different actions or calculations based on multiple criteria or conditions. You can use nested IF functions or other functions that can handle multiple criteria, such as AND, OR, and NOT functions, to test multiple conditions and return different results based on the combination of conditions. You can also use the IFS and SWITCH functions, which are available in Excel 2019 or later, or Excel for Microsoft 365, as alternatives to nested IF functions or IF functions with AND, OR, and NOT functions. These functions can simplify your formulas and make them easier to read and maintain.

Example

To illustrate how to use the IF function with multiple conditions in Excel, let’s consider a scenario where you have a table showing the names, genders, ages, and salaries of some employees. You want to use the IF function with multiple conditions to calculate the bonus for each employee based on the following criteria:

• If the employee is a male and older than 40, the bonus is 10% of the salary.
• If the employee is a female and younger than 30, the bonus is 15% of the salary.
• If the employee is neither of the above, the bonus is 5% of the salary.

Here is a possible table with the data and the formula:

The formula in column E uses the IF function with the AND function to test multiple conditions and return the bonus amount based on the criteria. The formula tests if the employee is a female and younger than 30, and returns the salary multiplied by 15% if it is. If not, the formula tests if the employee is a male and older than 40, and returns the salary multiplied by 10% if it is. If neither of the conditions is true, the formula returns the salary multiplied by 5%.

Here is the result of the formula:

As you can see, the formula correctly calculates the bonus for each employee based on the multiple conditions.

Other Approaches

Besides using the IF function with multiple conditions, you can also use other approaches to achieve the same result. For example, you can use the following IFS function to calculate the bonus for each employee based on the same criteria:

=IFS (AND (B2=“Female”, C2<30), D20.15, AND (B2=“Male”, C2>40), D20.1, TRUE, D2*0.05)

This formula is equivalent to the IF function with the AND function shown earlier, but it is shorter and easier to understand. The IFS function tests each condition in order, and returns the corresponding value if the condition is true. If none of the conditions is true, the formula returns the last value, which is the salary multiplied by 5%.

You can also use the following SWITCH function to calculate the bonus for each employee based on the same criteria:

=SWITCH (TRUE, AND (B2=“Female”, C2<30), D20.15, AND (B2=“Male”, C2>40), D20.1, D2*0.05)

This formula is equivalent to the IF function with the AND function shown earlier, but it is simpler and more concise. The SWITCH function evaluates the expression, which is TRUE in this case, and returns the value that matches the first true case. If none of the cases is true, the formula returns the last value, which is the salary multiplied by 5%.