Excel's IF function runs a logical comparison and outputs a result as either TRUE or FALSE. For the example below, students who have secured a score of 50 or above will receive “PASS” or will receive “FAIL”. The formula for this would be: =IF(C5>=70,”PASS”,”FAIL”).
This is a very basic demonstration of the IF function. A complex formula may test more than one condition by nesting IF functions. The IF function can also be combined with other logical functions like AND and OR to extend the logical test. The result from the IF function can be a value, a cell reference, or even another formula.
Syntax
=IF(logical_test,[value_if_true],[value_if_false])Arguments
logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
value_if_false - [optional] The value to return when logical_test evaluates to FALSE.Users must understand the following to avoid confusion while using the IF function:
- The first argument of IF functions is a logical_test. It is generally an expression that returns a TRUE or FALSE as output.
- The second argument is value_if_true, the value to return when logical_test is TRUE.
- The last argument is value_if_false, the value to return when logical_test is FALSE.
Both value_if_true and value_if_false are optional, but you must provide one or the other. For example, if cell A1 contains 60, then:
=IF(A1>50,TRUE) // returns TRUE
=IF(A1>50,"OK") // returns "OK"
=IF(A1<50,"OK") // returns FALSE
=IF(A1>55,"Yes","No") // returns "Yes"
=IF(A1>85,"Yes","No") // returns "No"Users must enclose any text values in a formula with double quotes (””) like “Yes”, “OK”, “Red”, “Green”, etc. But, numeric values or cell reference does not need to be enclosed in quotes.
Logical tests
The IF function uses logical operators such as “>, <, <>, =” when creating logical comparisons. Most commonly, the logical_test in IF is a complete logical expression that will evaluate to TRUE or FALSE. The table below shows some common examples:
| Goal | Logical test |
|---|---|
| If A1 is greater than 85 | A1>85 |
| If A1 equals 90 | A1=90 |
| If A1 is less than or equal to 100 | A1<=100 |
| If A1 equals “Green” | A1=”Green” |
| If A1 is not equal to “Orange” | A1<>”Orange” |
| If A1 is less than B1 | A1<B1 |
| If A1 is empty | A1=”” |
| If A1 is not empty | A1<>”” |
| If A1 is less than current date | A1<TODAY() |
Notice that the text values are enclosed in double quotes (“”), but the numbers are not as mentioned earlier. Also, note that the IF function can be combined with IF and COUNTIF functions but it does not support wildcards.
Example 1: Assign Pass or Fail
As shown in the previous example, we will assign either “PASS” or “FAIL” based on a test score. A passing score is 50 or higher. The formula in D5 is:
=IF(C5>=50,"PASS","FAIL")Interpretation: If the value in C5 is greater than or equal to 50, assign “PASS”. Otherwise, assign “FAIL”.
Note that the logical flow of this formula can be reversed and the formula returns the same result:
=IF(C5<50,"FAIL","PASS")Interpretation: If the value in C5 is less than 50, assign “FAIL”. Otherwise, assign “PASS”.
Both formulas above, when copied down, will return correct results.
Example 2: Assign points based on color
Consider a situation where we want to assign points based on the color names in column B. If the color is “Green”, the result should be 150. If the color is “Yellow”, the result should be 115. To achieve this, we need to use a formula with two IF functions, one NESTED inside the other. The formula is:
=IF(B5="Green",150,IF(B5="Yellow",115))Interpretation: IF the value in B5 is “Green”, the result is 150. Else, if the value in B5 is “Yellow”, the result is 115.
There are three things to be noted down from this example:
- The formula will give the result “FALSE” if the value in B5 is anything except “Green” or “Yellow”
- The values, “Green” and “Yellow”, must be enclosed in double quotes (“”) because they are text.
- The IF function is not case-sensitive and will match “Green”, “GREEN”, “GeeN”, or “gREEN”.
Example 3: Return another formula
The IF function can also return another formula as a result. For example, the formula below will return A1*15% when A1 is less than 200, and A1*10% when A1 is greater than or equal to 100:
=IF(A1<200,A1*15%,A1*10%)Example 4: Nested IF statements
The IF function can also be nested. A “nested IF” refers to a formula where at least one IF function is nested inside another in order to compare more conditions and return more possible results. Each IF statement needs to be carefully “nested” inside another so that the logic is correct. For example, the following formula can be used to assign a letter grade:
=IF(C6<50,"F",IF(C6<60,"D",IF(C6<70,"C",IF(C6<80,"B","A"))))As for the limit, up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP, HLOOKUP, XLOOKUP, INDEX, or MATCH for more complex scenarios where relevant, because they can handle more conditions in a more streamlined fashion.
Example 5: IF with AND, OR, NOT
The AND function and the OR function can be combined with the IF function for better results where needed. Let's consider the example below: to return “Good” when A1 is between 15 and 30, you can use a formula with AND like this:
=IF(AND(A1>15,A1<30),"Good","")You can use the OR function like below to return “Okay” when A1 is “Green” or “Yellow”:
=IF(OR(A1="Green",A1="Yellow"),"Okay","Not Okay")You can use the NOT function like below to return “No Match” when A1 is “Yellow”:
=IF(NOT(A1="Green"),"Value is not Green","Green")The above examples and explanation would help you get started with the IF function of Excel. The practical use IF function is vast and a single cannot article cannot explain enough. You will find a lot of examples and use cases of the IF function on this website. Do check those out and happy learning!
