Explanation

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 else in Excel

The concept of “If else” in Excel is handled with the IF function . The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:

=IF(test,true_result,false_result)

For example, to check cell A1 and return “Yes” if the value is greater than 100 and “No” if not, you can use the IF function like this:

=IF(A1>100,"Yes","No")

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.

Example worksheet problem

In the worksheet 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:

  1. If a cell in column D contains “S”, return “Small”.
  2. 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  (if elseif)

As seen above, handling a single condition with the IF statement is simple. But how do you implement the idea of “If elseif” in Excel? The formula above works fine if we only have two sizes, Small (“S”) and Large (“L”), but what if we have another size, “M” for “Medium”? In that case, we can 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"))
Nested IF function example - 1

Roughly translated, the formula now means: “If D5 is “S” return “Small”, elseif D5 is “M” return “Medium”, else return “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” or “Nested IFs” for short. 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:

  1. The IFS function is designed to handle multiple options without nesting.
  2. The VLOOKUP function can handle many options with a simple formula .

Explanation

The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches are explained below.

Nested IF solution

In the worksheet shown, the formula in cell G5 is:

=IF(B5<>"",B5,IF(C5<>"",C5,IF(D5<>"",D5,IF(E5<>"",E5,"no value"))))

In Excel, empty double quotes (”") mean an empty string . The <> symbol is a logical operator that means “not equal to”, so the following expression means “A1 is not empty”:

=B5<>"" // B5 is not empty

The overall structure of this formula is what is called a " nested IF formula “. Each IF statement checks a cell to see if a particular cell is not empty. If a cell is not empty , the IF returns the value from that cell. If the cell is empty , the IF statement hands off processing to the following IF function. The flow of a nested IF is somewhat easier to understand if we add line breaks to the formula to separate each IF function like this:

=
IF(B5<>"",B5,
IF(C5<>"",C5,
IF(D5<>"",D5,
IF(E5<>"",E5,
"no value"))))

With ISBLANK

It is also possible to use the ISBLANK function , which returns TRUE when a cell is blank:

=ISBLANK(A1) // A1 is blank

The behavior can be “reversed” by nesting the ISBLANK function inside the NOT function :

=NOT(ISBLANK(A1)) // A1 is not blank

The original formula above can be re-written to use ISBLANK as follows:

=IF(NOT(ISBLANK(B5)),B5,IF(NOT(ISBLANK(C5)),C5,IF(NOT(ISBLANK(D5)),D5,IF(NOT(ISBLANK(E5)),E5,"no value"))))

XLOOKUP solution

Another way to solve this problem is with the XLOOKUP formula like this:

=XLOOKUP(TRUE,ISNUMBER(B5:E5),B5:E5)

We can use XLOOKUP in this case because the four cells in B5:E5 are together, so they can be provided as a single range. The ISNUMBER function checks the range and returns an array of TRUE and FALSE values like this:

{TRUE,TRUE,TRUE,TRUE}