The Excel IF function uses a logical test and returns two values, one result for TRUE and another for FALSE.
The IF function in Excel is a powerful tool for performing logical comparisons and getting different results based on whether a specific condition is met. It evaluates a logical test: a statement or condition that can be TRUE or FALSE. Based on the result of this test, the IF function returns one of two specified results: one for when the condition is TRUE and another for when it is FALSE.
How to use the IF Function
Use the IF function to perform a logical test and the greater than logical operator to check the condition. For example, if the value in cell A1 is greater than 100, the result is TRUE; else, it is FALSE.
=IF(A1 > 100, ”High”, ”Low”)
Syntax, Arguments
The syntax is simple:
=IF(logical test, [value if true], [value if false])
The function uses three arguments: one required and two optional.
- logical_test: a single value or a logical expression. The result is a TRUE or FALSE that follows the boolean logic.
- value_if_true: the result if the logical_test evaluates to TRUE (optional argument)
- value_if_false: the result if the logical_test evaluates to FALSE (optional argument)
IF and Logical operators
The standalone IF function is effective. However, it is worth combining with other logical functions for extended usability. In the example below, you can create custom tests if you are using the following logical operators for comparison purposes:
| Logical operator | Note | Example |
|---|---|---|
| = | equal to | A1 = B1 |
| > | greater than | A1 > B1 |
| >= | greater than or equal to | A1 >= B1 |
| < | less than | A1 < B1 |
| <= | less than or equal to | A1 <= B1 |
| <> | not equal to | A1 <> B1 |
Using IF with logical functions: AND, OR, NOT
You can create complex criteria by combining the IF function with the AND or OR function. In the first example, we create a formula that returns “Found” when the number in cell B2 is greater than 5 and less than 20. Otherwise, the formula uses the third argument and returns “not found”.
=IF(AND(B2>5,B2<20,”Found”,””)
Explanation: First, the AND function checks both criteria. In the case of B2>5 and B2<20, the formula returns with TRUE. IF function will use the result as the first argument and return with “match”. Otherwise, the formula returns “no match“.
Here is an IF and OR example; create a formula using the following criteria. If B2 is equal to 10 or 20, return the “match found” value; else, display “no match”.
=IF(OR(B2=10,B2=20),”match found”,”no match”)
In the third example, we have complex criteria: If B2 is between 10 and 20, or B3 is between 80 and 100, return the message “match found“ or “no match“.
=IF(OR(AND(B2>10,B2<20), AND(B3>80,B2<100),”match found”,”no match”)
Finally, here is an example of the IF and NOT functions. If B2 is NOT “t-shirt”, return B2 * 0.9; otherwise, return B2.
=IF(NOT(B2=”t-shirt”), B2*0.9 ,B2)
Nested IF statements
Since we have the IFS function, the nested IF solutions seem outdated. You can use a maximum of 128 IF functions. A “nested IF” refers to a formula that contains another IF function. In this case, you can test more conditions in a single formula, and variable outputs are available.
Here is a simple rating example, test cell B2 and return the corresponding value based on the following rules: 1= “bad”, 2 = “poor”, 3 = average, 4 = “good”, 5 = “excellent”
Check the rating in cell B3:
=IF(B3=1,”bad”,IF(B3=2,”poor”,IF(B3=3,”average”,IF(B3=4, “good”,IF(B3=5,”excellent”)))))
Here is the simplified formula using IFS:
=IFS(B3=1,”bad”,B3=2,”poor”,B3=3,”average”,B3=4,”good”,B3=5,”best”)
Comparing the formula structures, we prefer the IFS function instead of using IF.
Use logical test without IF
Because the output is always TRUE or FALSE, we’ll use a formula without functions in the following example.
=A1 > 100
I think no further explanation is necessary. If the price is greater than 100, the result is TRUE. Otherwise, the formula returns FALSE.