Description
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.
Syntax
Arguments
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
Return value
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:
= IF(C8>=50, “Pass”, “Fail”)
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:
= IF(E1=“Done”, 1, 0)
However, for logical tests on numerical values you can use any logical operator and perform additional calculations.
Logical Operators
Here is an overview of all logical operators you can use to construct a logical test with IF:
Logical Operator | Meaning | Example |
|---|---|---|
= | equal to | A1 = C1 |
<> | not equal to | A1 <> C1 |
> | greater than | A1 > C1 |
< | smaller than | A1 < C1 |
>= | greater than or equal to | A1 >= C1 |
<= | smaller than or equal to | A1 <= C1 |
Combination of IF with AND & OR
if you want to test multiple conditions in your IF statement at once, you can use the AND function or OR function to check if either all conditions or at least one of multiple conditions are TRUE.
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:
- IFS function as an alternative to nested IF statement
- VLOOKUP, HLOOKUP or XLOOKUP function instead of nested IF statement
- SWITCH function as a compact form of nested IF statement
- CHOOSE function instead of nested IF statement
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
Related Tutorials
Related Functions
Excel IFS function
The Excel IFS function allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result
Excel OR function
The Excel OR function is a logical function that is used to check if at least one of multiple conditions is evaluate TRUE.
Excel AND function
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.
