How to Use IF Function in Excel with 3 Conditions?

Sharing is caring!

The IF function in Excel is one of the most powerful tools for making decisions based on specific conditions. When you need to evaluate multiple conditions (like three different scenarios), understanding how to properly structure these formulas becomes essential for efficient data analysis and reporting.

In this guide, we will walk you through everything you need to know about using the IF function with three conditions in Excel, from basic concepts to practical examples and advanced techniques.

Understanding the IF Function Basics

Before diving into multiple conditions, it’s important to understand how the basic IF function works in Excel.

Basic IF Function Syntax

The fundamental syntax of an IF function is:

=IF(logical_test, value_if_true, value_if_false)

Where:

• logical_test: The condition you want to evaluate (returns TRUE or FALSE)
• value_if_true: The result if the condition is TRUE
• value_if_false: The result if the condition is FALSE

How the IF Function Works

When Excel processes an IF function, it first evaluates the logical test. If that test returns TRUE, Excel provides the “value_if_true” result. If the test returns FALSE, Excel gives the “value_if_false” result.

Creating IF Functions with Three Conditions

There are two main approaches to handling three conditions in Excel:

1. Nested IF functions (available in all Excel versions)
2. IFS function (available in Excel 2019 and later versions)

Method 1: Using Nested IF Functions

Nested IF functions involve placing one IF function inside another to evaluate multiple conditions sequentially. This is the traditional method that works in all versions of Excel.

Nested IF Syntax for Three Conditions

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

In this structure:

• Excel first checks condition1
• If condition1 is TRUE, it returns result1
• If condition1 is FALSE, it checks condition2
• If condition2 is TRUE, it returns result2
• If condition2 is FALSE, it checks condition3
• If condition3 is TRUE, it returns result3
• If condition3 is FALSE, it returns result4

Example of Nested IF with Three Conditions

Let’s say you want to grade test scores:

• If the score is above 90, grade is “A”
• If the score is between 80-90, grade is “B”
• If the score is between 70-80, grade is “C”
• If the score is below 70, grade is “D”

The formula would be:

=IF(A1>90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "D")))

Method 2: Using the IFS Function (Excel 2019+)

The IFS function was introduced in Excel 2019 to simplify working with multiple conditions. It evaluates conditions in order and returns the value corresponding to the first TRUE condition.

IFS Function Syntax

=IFS(condition1, result1, condition2, result2, condition3, result3, TRUE, default_result)

Example of IFS Function with Three Conditions

Using the same grading example:

=IFS(A1>90, "A", A1>=80, "B", A1>=70, "C", TRUE, "D")

The TRUE at the end serves as a catch-all for any values that don’t meet the previous conditions.

Practical Examples of IF with Three Conditions

Let’s explore some practical scenarios where using IF with three conditions is valuable.

Example 1: Sales Commission Calculator

Suppose you need to calculate sales commissions based on performance:

• Sales over $10,000: 10% commission
• Sales between $5,000 and $10,000: 7% commission
• Sales between $1,000 and $5,000: 5% commission
• Sales below $1,000: 2% commission

Using Nested IF Functions:

=IF(A1>10000, A1*0.1, IF(A1>5000, A1*0.07, IF(A1>1000, A1*0.05, A1*0.02)))

Using IFS Function:

=IFS(A1>10000, A1*0.1, A1>5000, A1*0.07, A1>1000, A1*0.05, TRUE, A1*0.02)

Example 2: Employee Performance Rating

Let’s create a formula to rate employee performance based on three metrics:

We’ll rate employees as “Outstanding,” “Satisfactory,” or “Needs Improvement” based on:

• Outstanding: Sales > 100% AND Customer Satisfaction > 4 AND Project Completion > 90%
• Satisfactory: Sales > 80% AND Customer Satisfaction > 3 AND Project Completion > 75%
• Needs Improvement: Any other combination

Formula:

=IF(AND(A1>100, A2>4, A3>90), "Outstanding", IF(AND(A1>80, A2>3, A3>75), "Satisfactory", "Needs Improvement"))

Tips for Working with Multiple Conditions in IF Functions

1. Keep Your Formulas Readable

When creating complex nested IF functions, it’s easy for formulas to become difficult to read. Consider these practices:

• Break complex formulas into separate cells
• Use named ranges to make formulas more readable
• Add comments to explain complex logic

2. Check for Logical Errors

When working with multiple conditions, make sure your conditions don’t overlap in unintended ways. For example:

=IF(A1>80, "High", IF(A1>70, "Medium", "Low"))

In this formula, the second condition (A1>70) will never trigger for values over 80 because the first condition catches them first.

3. Use Tables to Plan Your Logic

Before creating complex IF formulas, map out your logic in a table:

4. Combine IF with Other Functions

You can make your conditional logic more powerful by combining IF with other functions:

• AND: Requires multiple conditions to be true
• OR: Requires at least one condition to be true
• NOT: Reverses a logical value

Example with AND:

=IF(AND(A1>80, B1="Complete"), "Passed", "Review Required")

Comparing Nested IF vs. IFS Function

Advanced Applications of IF with Three Conditions

Combining Text and Calculations

You can combine text with your calculation results:

=IF(A1>90, "Grade A - " & A1 & " points", IF(A1>80, "Grade B - " & A1 & " points", "Below B Grade"))

Using IF with Date Conditions

You can use IF to evaluate dates:

=IF(TODAY()-A1>30, "Overdue", IF(TODAY()-A1>15, "Due Soon", "On Track"))

Error Handling in Complex IF Formulas

Add error handling to prevent issues:

=IF(ISNUMBER(A1), IF(A1>90, "A", IF(A1>80, "B", "C")), "Invalid Input")

Final Thoughts

Mastering the IF function with three conditions in Excel gives you powerful tools for data analysis and decision-making. While nested IF functions work in all Excel versions, the newer IFS function provides a more streamlined approach for handling multiple conditions.

Remember to test your formulas thoroughly with different input values to ensure they produce the expected results in all scenarios.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.