The Excel IF function is one of the most used and powerful functions in Excel. It allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.
logical_test – The condition that you want to test
value_if_true – [Optional] The result you want to be returned if the logical_test result is TRUE
value_if_false – [Optional] The result you want to be returned if the logical_test result is FALSE
The corresponding value that you define for TRUE or FALSE.
How to use the IF function in Excel
Use the IF function to perform a logical test with specific return values for a TRUE and FALSE result.
In the above example, we want to assign either a “Pass” or “Fail” based on a given exam score. In order to pass the test 50 or more points are required. The formula in D8 is:
The IF function can be used to evaluate both values and text. For logical tests on text you are limited to checking if one thing is equal to another, so you can only use the “=” operator in the logical test:
However, for logical tests on numerical values you can use any logical operator and perform additional calculations.
Here is an overview of all logical operators you can use to construct a logical test with IF:
A1 = C1
not equal to
A1 <> C1
A1 > C1
A1 < C1
greater than or equal to
A1 >= C1
smaller than or equal to
A1 <= C1
Combination of IF with AND & OR
Let’s say you want to return “Good” if the value in A1 is between 15 and 20 and “Bad” if the value is outside this range, then you can use the following formula with AND inside the IF function:
To return “Red” if A1 is either “Hearts” or “Diamonds”, you can use OR inside the IF function:
Nested IF functions
To test multiple conditions and return different values depending on the results, you can nest multiple IF functions inside each other. “Nested” in this context means that you insert another IF statement for the value_if_true or value_if_false argument of another IF statement.
The most common structure of a nested IF statement looks as follows:
IF( logical_test_1, result_1, IF( logical_test_2, result_2, IF( logical_test_3, result_3, result_4)))
As an example, let’s assign different grades to the exam scores in our above example instead of only “Pass” or “Fail”:
Even though nesting the IF function is possible for up to 64 IF functions, we recommend to use an alternative function for more complex scenarios. Here is a list of alternative functions for checking multiple conditions:
Special IF functions
In Excel there are many functions that can be considered to be special cases of the IF function. On the one hand, there are functions that save you time as the logical test is already predefined:
- IFERROR function to handle errors in a cell and return a supplied value instead of the error
- IFNA function to handle NA errors in a cell and return a supplied value instead of the error
On the other hand, there are many functions that allow you to apply simple calculation tasks on arrays of values depending on a logical test applied for every single cell in the array:
- SUMIF and SUMIFS function to add cell values specified by a given criteria
- COUNTIF and COUNTIFS function to count the number of cells that meet the given criteria
- AVERAGEIF and AVERAGEIFS function to return the average of cell values that meet the given criteria
- MAXIFS and MINIFS function to return the maximum or minimum value among cells specified by a given set of criteria
AND formula examples
The Excel AND function is a logical function that is used to check multiple conditions at the same time. AND returns TRUE if all conditions evaluate TRUE.
The Excel OR function is a logical function that is used to check if at least one of multiple conditions is evaluate TRUE.
The Excel IFS function allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result