Excel Campus - SoundSage

Bottom Line: Master the IF function with three conditions to make your Excel data analysis more powerful.

Skill Level: Intermediate

Download the Excel FileYou can download the Excel file that has the examples from this post here:

If Function with 3 ConditionsDownloadThe IF Function with 3 ConditionsThe IF function in Excel allows you to test a condition and return one value if the condition is TRUE and another if it's FALSE. (See this tutorial for a detailed explanation of how to write an IF function). But what if you need to check multiple conditions?

In this post, we’ll walk through how to use the IF function with three conditions to help you handle more complex logic.

Let’s break down how you can use nested IF functions or the IFS function to achieve this.

Using Nested IF FunctionsYou can use nested IF functions to test multiple conditions in Excel. With this approach, you combine several IF statements to evaluate three conditions, one after the other.

Steps:

Start by typing your IF function in the desired cell.
The basic syntax is:
=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result_if_all_false)))
Excel will evaluate each condition in sequence until it finds a TRUE result or returns the final FALSE result.

In the example below, the nested functions test multiple conditions to determine how to rate a test score.

If the test score is greater than 90, return “Excellent.”
If the test score is greater than 80, return “Good.”
If the test score is greater than 70, return “Average.”
If none of the above conditions are met, return “Poor.”

The formula reads =IF(A2>60, “Pass”, IF(B2>60, “Pass”, IF(C2>60, “Pass”, “Fail”)))

Using the IFS FunctionIn Excel 2016 and later versions, the IFS function simplifies checking multiple conditions. Instead of nesting IF statements, you can use IFS to test all conditions in one function.

Steps:

Select the cell where you want the result.
The basic syntax for the IFS function is:
=IFS(condition1, result1, condition2, result2, condition3, result3)
Excel will evaluate each condition in order and return the result of the first TRUE condition.

Below, you find the same test score example as above, but written using the IFS function.

=IFS(A2>90, “Excellent”, B2>80, “Good”, C2>70, “Average”, TRUE, “Poor”)

IFS checks multiple conditions in order of priority:

Bonus Tip: Combining with AND/ORYou can further enhance your IF function by combining it with AND or OR to test if multiple conditions are met at the same time or if any condition is true.

AND: All conditions must be TRUE.
OR: Only one condition needs to be TRUE.

For example, you can use:
=IF(AND(condition1, condition2, condition3), result_if_true, result_if_false)

In the example below, the formula checks if all three test scores (columns A, B, and C) are greater than 60 using the AND function. If they are, it returns “Pass”; if not, it returns “Fail.”

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

ConclusionBy using nested IF, IFS, or combining with AND/OR, you can handle more complex logic in Excel. Mastering these functions will help you make smarter decisions based on your data!