As the formula is copied down, it returns "Small" when the value in column D is "S" and "Large" when the value in column D is "L". Note that
text values
within the formula must be enclosed in double quotes ("").
The goal is to return "Small" when the value in column D is "S" and "Large" when the value in column D is "L". In other words, if the value in column D is "S" return "Small" else return "Large".
IF function
The
IF function
in Excel runs a test, then performs one action if the result is TRUE, and a different action if the result is FALSE. The generic syntax for IF looks like this:
=IF(test,true_result,false_result)
Note that the "else" concept is built into the IF function. The first argument is the logical test, and the second argument is the result (or calculation) to return when the test is TRUE. The third argument is the "else" — the value or calculation to return if the result of the logical test is FALSE.
In the example shown, we have a list of T-shirts that includes color and size. The sizes in column D are abbreviated, with "S" for small and "L" for large. There are only these two sizes in the data. Let's say you want to write a formula to expand these abbreviations and show either the word "Small" or "Large" in column E. In other words:
If a cell in column D contains "S", return "Small".
If a cell in column D contains "L", return "Large".
This is a perfect application of the IF function. To check the abbreviated size in column D and return either "Small" or "Large", the formula in cell E5 is:
=IF(D5="S","Small","Large")
Translated, this means:
IF cell D5 equals "S", return "Small", ELSE return "Large".
Notice we are only testing for "S" — we don't need to test for "L". That's because we only have two possible values, and the ELSE part of the formula (the FALSE result) logically takes care of "L" for us: if the cell doesn't contain "S", it
must be
"L".
Text values inside the IF function must be enclosed in double quotes (""), but numbers should
not
be quoted. See our
IF Function page
for more details.
Nesting IFs to handle more conditions
This approach works well for two conditions, however, it won't handle a third condition. For example, what if we have another size, "M" for "Medium"? In that case, we need to extend the formula with another IF statement. We do this by replacing the existing FALSE result with a second IF function. In the example below, we've extended the formula to handle a medium size. The formula in E5 is:
=IF(D5="S","Small",IF(D5="M","Medium","Large"))
This technique is called "
nesting
" since we are placing one function
inside
another. When more than one IF function is nested together in a formula, you will sometimes hear the formula called a "Nested IF formula".
This page has many examples
.
Other options
It is possible to nest many IF functions together in a single formula. However, the longer a formula like this gets, the harder it is to read and maintain. Before you create a long nested IF formula, you should consider other options:
The
IFS function
is designed to handle multiple options without nesting.
The
VLOOKUP function
can handle
many
options with a
simple formula
.
In the example shown, we want to mark or "flag" records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word "red" or "green". In D6, the formula is: =IF(OR(B6="red",B6="green"),"x","") This is an example of nesting...
The goal is to "flag" records that are neither "Red" nor "Green". More specifically, we want to check the color in column B, and leave an "x" in rows where the color is NOT "Red" OR "Green". If the color is "Red" OR "Green", we want to display nothing. IF function logic The IF function is commonly...
Quick, clean, and to the point training
Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.
View Paid Training & Bundles
