In this article, you will learn how to build an Excel IF statement for different types of values as well as how to create multiple IF statements.
IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met.
In this tutorial, we are going to learn the syntax and common usages of the Excel IF function, and then take a closer look at formula examples that will hopefully prove helpful to both beginners and experienced users.
IF function in Excel
IF is one of logical functions that evaluates a certain condition and returns one value if the condition is TRUE, and another value if the condition is FALSE.
The syntax of the IF function is as follows:
IF[logical_test, [value_if_true], [value_if_false]]
As you see, IF takes a total of 3 arguments, but only the first one is obligatory, the other two are optional.
Logical_test [required] - the condition to test. Can be evaluated as either TRUE or FALSE.
Value_if_true [optional] - the value to return when the logical test evaluates to TRUE, i.e. the condition is met. If omitted, the value_if_false argument must be defined.
Value_if_false [optional] - the value to return when the logical test evaluates to FALSE, i.e. the condition is not met. If omitted, the value_if_true argument must be set.
Basic IF formula in Excel
To create a simple If then statement in Excel, this is what you need to do:
- For logical_test, write an expression that returns either TRUE or FALSE. For this, you'd normally use one of the logical operators.
- For value_if_true, specify what to return when the logical test evaluates to TRUE.
- For value_if_false, specify what to return when the logical test evaluates to FALSE. Though this argument is optional, we recommend always configuring it to avoid unexpected results. For the detailed explanation, please see Excel IF: things to know.
As an example, let's write a very simple IF formula that checks a value in cell A2 and returns "Good" if the value is greater than 80, "Bad" otherwise:
=IF[B2>80, "Good", "Bad"]
This formula goes to C2, and then is copied down through C7:
In case you wish to return a value only when the condition is met [or not met], otherwise - nothing, then use an empty string [""] for the "undefined" argument. For example:
=IF[B2>80, "Good", ""]
This formula will return "Good" if the value in A2 is greater than 80, a blank cell otherwise:
Excel If then formula: things to know
Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic.
If value_if_true is omitted
If the 2nd argument of your Excel IF formula is omitted [i.e. there are two consecutive commas after the logical test], you'll get zero [0] when the condition is met, which makes no sense in most cases. Here is an example of such a formula:
=IF[B2>80, , "Bad"]
To return a blank cell instead, supply an empty string [""] for the second parameter, like this:
=IF[B2>80, "", "Bad"]
The screenshot below demonstrates the difference:
If value_if_false is omitted
Omitting the 3rd parameter of IF will produce the following results when the logical test evaluates to FALSE.
If there is just a closing bracket after value_if_true, the IF function will return the logical value FALSE. Quite unexpected, isn't it? Here is an example of such a formula:
=IF[B2>80, "Good"]
Typing a comma after the value_if_true argument will force Excel to return 0, which doesn't make much sense either:
=IF[B2>80, "Good",]
The most reasonable approach is using a zero-length string [""] to get a blank cell when the condition is not met:
=IF[B2>80, "Good", ""]
Tip. To return a logical value when the specified condition is met or not met, supply TRUE for value_if_true and FALSE for value_if_false. For the results to be Boolean values that other Excel functions can recognize, don't enclose TRUE and FALSE in double quotes as this will turn them into normal text values.
Using IF function in Excel - formula examples
Now that you are familiar with the IF function's syntax, let's look at some formula examples and learn how to use If then statements in real-life scenarios.
Excel IF function with numbers
To build an IF statement for numbers, use logical operators such as:
- Equal to [=]
- Not equal to []
- Greater than [>]
- Greater than or equal to [>=]
- Less than [