Purpose
Return value
Syntax
=IF(logical_test,[value_if_true],[value_if_false])
- 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.
How to use
The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The result from IF can be a value, a cell reference, or even another formula. By combining the IF function with other logical functions like AND and OR, you can test more than one condition at a time.
Basic example
In the worksheet below, the results in column F are based on the IF formulas shown in column D:
The code block below summarizes the formulas and results in text format:
=IF(B5>75,TRUE) // returns TRUE
=IF(B5>75,"OK") // returns "OK"
=IF(B5>85,"OK") // returns FALSE
=IF(B5>75,10,0) // returns 10
=IF(B5>85,10,0) // returns 0
=IF(B5>75,"Yes","No") // returns "Yes"
=IF(B5>85,"Yes","No") // returns "No"
Notice that text values like "OK", "Yes", "No", etc. must be enclosed in double quotes (""). However, numeric values should not be enclosed in quotes.
Logical tests
The IF function supports logical operators (>,<,<>,=) when creating logical tests. 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 75 | A1>75 |
If A1 equals 100 | A1=100 |
If A1 is less than or equal to 100 | A1<=100 |
If A1 equals "Red" | A1="red" |
If A1 is not equal to "Red" | A1<>"red" |
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 text values must be enclosed in double quotes (""), but numbers do not. The IF function does not support wildcards, but you can combine IF with COUNTIF to get basic wildcard functionality. To test for substrings in a cell, you can use the IF function with the SEARCH function.
Pass or Fail example
In the worksheet shown above, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:
=IF(C5>=70,"Pass","Fail")
Translation: If the value in C5 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".
Note that the logical flow of this formula can be reversed. This formula returns the same result:
=IF(C5<70,"Fail","Pass")
Translation: If the value in C5 is less than 70, return "Fail". Otherwise, return "Pass".
Both formulas above, when copied down, will return correct results.
Note: If you are new to the idea of formula criteria, this article explains many examples.
Assign points based on color
In the worksheet below, we want to assign points based on the color in column B. If the color is "red", the result should be 100. If the color is "blue", the result should be 125. This requires that we use a formula based on two IF functions, one nested inside the other. The formula in C5, copied down, is:
=IF(B5="red",100,IF(B5="blue",125))
Translation: IF the value in B5 is "red", return 100. Else, if the value in B5 is "blue", return 125.
There are three things to notice in this example:
- The formula will return FALSE if the value in B5 is anything except "red" or "blue"
- The text values "red" and "blue" must be enclosed in double quotes ("")
- The IF function is not case-sensitive and will match "red", "Red", "RED", or "rEd".
This is a simple example of a nested IFs formula. See below for a more complex example.
Return another formula
The IF function can return another formula as a result. For example, the formula below will return A1*5% when A1 is less than 100, and A1*7% when A1 is greater than or equal to 100:
=IF(A1<100,A1*5%,A1*7%)
Nested IF statements
The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for 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 grade rather than a pass / fail result:
=IF(C6<70,"F",IF(C6<75,"D",IF(C6<85,"C",IF(C6<95,"B","A"))))
Up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP or XLOOKUP for more complex scenarios, because they can handle more conditions in a more streamlined fashion. For a more details see this article on nested IFs.
Note: the newer IFS function is designed to handle multiple conditions without nesting. However, a lookup function like VLOOKUP or XLOOKUP is usually a better approach unless the logic for each condition is custom.
IF with AND, OR, NOT
The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use a formula like this:
=IF(AND(A1>7,A1<10),"OK","")
Translation: if A1 is greater than 7 and less than 10, return "OK". Otherwise, return nothing ("").
To return B1+10 when A1 is "red" or "blue" you can use the OR function like this:
=IF(OR(A1="red",A1="blue"),B1+10,B1)
Translation: if A1 is red or blue, return B1+10, otherwise return B1.
=IF(NOT(A1="red"),B1+10,B1)
Translation: if A1 is NOT red, return B1+10, otherwise return B1.
IF cell contains specific text
Because the IF function does not support wildcards, it is not obvious how to configure IF to check for a specific substring in a cell. A common approach is to combine the ISNUMBER function and the SEARCH function to create a logical test like this:
=ISNUMBER(SEARCH(substring,A1)) // returns TRUE or FALSE
For example, to check for the substring "xyz" in cell A1, you can use a formula like this:
=IF(ISNUMBER(SEARCH("xyz",A1)),"Yes","No")
Read a detailed explanation here.
More information
- Read more about nested IFs
- Learn how to use VLOOKUP instead of nested IFs (video)
- 50 Examples of formula criteria