How to Use IF Function with 3 Conditions

At its core, the IF function examines a given condition and returns one result if the condition is true and another if it’s false.

But what if you need to check multiple conditions simultaneously? That’s where using the IF function with three conditions comes into play.

In this article, we’ll explore various methods of incorporating the IF function with three conditions to help you accomplish more in your data analysis and decision-making.

Let’s get started!

How To Use The IF Function with 3 Conditions (or Multiple Conditions)

You can create powerful multi-condition formulas by combining the IF function with AND, OR, and NOT functions! Let’s learn how they work and their syntax.

AND Function

The syntax for the AND function is =AND(condition1, condition2, …). This function returns TRUE only if ALL the specified conditions are met. Otherwise, it returns FALSE. 

For example, if you want to check whether a student passed both the written and practical exams, you can use the AND function within an IF statement like so:

=IF(AND(Written="pass", Practical="pass"), "Pass", "Fail")

OR Function

The syntax for the OR function is =OR(condition1, condition2, …). This function returns TRUE if ANY of the specified conditions are met and FALSE if none are satisfied.

Imagine you want to check whether a customer is eligible for a discount if they are a senior citizen or a student. An example of using the OR function within an IF statement would be:

=IF(OR(Senior="Yes", Student="Yes"), "Discount", "No Discount")

NOT Function

The syntax for the NOT function is =NOT(condition). This function returns the opposite of the given condition, meaning TRUE becomes FALSE and vice versa.

To check if a train is running late, you can use the NOT function within an IF statement like this:

=IF(NOT(OnTime="Yes"), "Late", "On Time")

Before we continue, take a moment to explore more about the logical functions in Excel if you need some additional help with these functions.

Method 1: Nested IF Function

A nested IF function allows you to test multiple conditions sequentially. This method involves placing one or more IF statements within another IF statement. The syntax for a nested IF function is:

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

Let’s dive into an example to see how it works. Imagine you want to determine if a cell value is positive, negative, or zero, and you want to return “Positive,” “Negative,” or “Zero.” This can be achieved using a nested IF function:

In this example, the nested IF function checks if the value in A2 is greater than 0 (condition1). If true, it returns “Positive.” If false, it checks if A2 is less than 0 (condition2). If true, it returns “Negative.” If neither condition is met, the final result is “Zero.”

To further understand the formula, let’s break down the arguments:

1. condition1: A2>0
2. value_if_true1: “Positive”
3. condition2: A2<0
4. value_if_true2: “Negative”
5. value_if_false2: “Zero”

Using nested IF functions comes with its advantages and disadvantages.

Advantages include:

• Easy to understand: Nested IF functions follow a sequential order, making them simple to comprehend
• Flexibility: This method can handle complex scenarios by adding additional conditions
• Adaptability: You can easily modify or extend the formula by adding, removing, or changing conditions

Disadvantages include:

• Prone to errors: The more conditions and nested levels you have, the greater the chances for mistakes or oversights
• Maintenance difficulties: Large and complex formulas with multiple conditions can be hard to maintain and troubleshoot
• Nesting limit: Excel only supports up to 64 levels of nesting.

Method 2: IF Function with AND Logic

In this method, you’ll learn how to combine the IF function with the AND logic to test multiple conditions that must all be true. This conditional logic enables you to evaluate several criteria simultaneously and assign a value accordingly.

The syntax for an IF function with AND logic is as follows:

=IF(AND(condition1, condition2, ...), value_if_true, value_if_false)

Let’s walk through an example. Imagine you’re checking if a student’s scores for three exams are all above 60. If the scores meet the criteria, we should return “Pass”; otherwise, the result is “Fail.” Here’s the formula:

In this example, the formula would be =IF(AND(B2>60, C2>60, D2>60), “Pass”, “Fail”). Let’s break down how the formula works:

• AND(A1>60, B1>60, C1>60) checks each score against the 60-point threshold. The AND function returns TRUE only if all conditions are met, meaning all scores are above 60.
• Then, inside the IF function, we provide the AND function as our test condition. If the AND function returns TRUE, our value_if_true is “Pass.“
• Finally, if the AND function returns FALSE (meaning at least one score is not above 70), our value_if_false is “Fail.“

This example demonstrates the power of combining the IF function with AND logic. It allows you to create complex criteria that require multiple conditions to be met simultaneously.

Method 3: IF Function with OR Logic

In this method, we’ll learn how to use the OR function inside the IF function to test multiple conditions where at least one must be true. The OR function allows you to build more flexible criteria by checking if any conditions are satisfied.

The syntax of an IF function with OR logic is as follows:

=IF(OR(condition1, condition2, ...), value_if_true, value_if_false)

Let’s consider an example of an IF formula with OR logic that checks if a cell value is equal to “Red” or “Green” and returns “Yes” or “No.” The formula we’ll use for this purpose looks like this:

Here’s how the formula works and what each argument means:

• OR(A2=”Red”, A2=”Green”): This part checks if the value in cell A2 is either “Red” or “Green.” If any of these conditions are true, the OR function returns TRUE; otherwise, it returns FALSE.
• value_if_true: If the OR function returns TRUE, the IF function returns “Yes.”
• value_if_false: If the OR function returns FALSE, the IF function returns “No.”

Remember, the power of OR is that it can handle even more conditions; just keep adding them to the list, separated by commas.

Method 4: IF Function with multiple logic operators

In this method, you’ll learn how to combine the AND, OR, and NOT functions inside the IF function to test multiple conditions with different logic types. Combining these functions allows you to create more sophisticated criteria that cover various scenarios.

The syntax:

=IF (AND/OR/NOT (condition1, condition2, ...), value_if_true, value_if_false)

Let’s say you want to check if a cell value is between 10 and 20 or not equal to 15 and return “Yes” or “No” as the result. Below is an example of an IF formula with AND and NOT logic that accomplishes this:

In this formula:

• AND(A1>=10, A1<=20, …) portion checks if the cell value is between 10 and 20.
• The NOT(A1=15) ensures the cell value is not equal to 15.
• IF(…, “Yes”, “No”) returns “Yes” if the AND conditions are met. If not, “No” will be returned.

By combining the AND, OR, and NOT functions with the IF function, you can effectively test various combinations of conditions and generate results based on the given logic.

If you find that the results are not what you expect, I suggest creating individual columns for each “test” so that you can see the result before combining several checks together.

Try the CHOOSE Function

You might be wondering if there is an alternative to using nested IF functions in Excel, and there is – the CHOOSE function comes in handy when you have a specific number of conditions to work with.

The CHOOSE function returns a value from a list based on a specified index number. The syntax is as follows:

=CHOOSE (index_num, value1, value2, ...)

Let’s say you want to return the weekday name based on a number from 1 to 7. The formula you’d use is:

If the value in cell A1 is 4, the formula returns Wednesday. The index number corresponds to the position of the weekday name, making it straightforward for you to modify or update based on your needs.

Here are some advantages and disadvantages of using the CHOOSE function to consider:

Advantages:

• Concise and straightforward, making it easier to use and understand
• Easy to modify when you need to add or remove values from your list

Disadvantages:

• Limited to 254 values, which may not cater to more extensive sets of conditions
• Requires a numeric index and might not work well with lookup functions like VLOOKUP for calculations

Common Mistakes and Troubleshooting

You might encounter some common mistakes and issues when working with Excel’s IF function, especially with multiple conditions.

Unexpected Results

This is by far the most common issue with multi-condition IF statements. Incorrect or unexpected results may arise when using multiple conditions with incorrect logic, order, or parentheses.

To resolve this, consider breaking down the formula into its component parts in separate columns. Then it’s easy to examine the logic and order of the conditions and use parentheses to group them correctly.

#VALUE! Error

This often occurs when using text values without quotation marks, incorrect cell references, or improper operators.

To fix this, double-check the syntax and spelling of your formula, use double quotes for text values and logical operators, and ensure valid cell references and operators are used.

#N/A Error

This can happen when using lookup functions like VLOOKUP or MATCH inside the IF function, and the lookup value is not found.

To solve this, check the accuracy and format of the lookup value and table array and use exact or approximate matches as needed. You may also use IFERROR or IFNA functions to handle errors.

#DIV/0! Error

The #DIV/0! error is another frequent issue that occurs when using arithmetic operations like division inside the IF function, and the divisor is zero.

To address this, verify the values of the cells involved in the operation and use nested IF functions or the IFERROR function to handle errors.

Frequently Asked Questions