One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula.
The
IF
formula is easy to understand and contains only three parameters which are all required. The first parameter is the
condition
to check, the second is the returned value if the condition is true, and the third parameter is the returned value if the condition evaluates to false.
=IF(condition,value_if_true,value_if_false)
Let’s look at a simple example:
A  

1  Hello 
2 
World
=IF(A1="Hello","World",)

IF
formula
As you can see from this simple example above, the
condition
(the first parameter of the
IF
formula) checks if the cell in
A1
contains the value
"Hello"
which, if it does, I want to return the value
"World"
. If it doesn’t contain
"Hello"
then I don’t want to return anything – this is why the third parameter of my
IF
formula contains nothing.
Condition Types
The primary purpose of the first parameter is to determine truthiness. This means you can use comparators such as equals or not equals, or greater than or less than another.
Besides using statements of comparison you can also perform truthy checks on the values of cells, for example:
A  B  

1 
Empty
=IF(LEN(A1),"Not Empty","Empty")


2 
FALSE

Is FALSE
=IF(A2,"Is TRUE","Is False")

Notice in the first
IF
formula the
condition
statement checks if the value in cell
A1
has a length. As the result of this statement is
0
this is interpreted as
FALSE
and therefore will return the value of the third parameter which is a string stating
Empty
.
In the second
IF
formula the
condition
just references the value contained in cell
A2
which has just the value
FALSE
. As this value does not result in
TRUE
again my third parameter is returned which is a string stating
Is False
.
While comparative statements in your
condition
are the means by what most people use
IF
statements, this highlights that you can use other statements, being aware that if they return
0
or an empty string
""
or an empty range the third parameter will be returned as these values are considered
FALSE
.
Multiple IF Statements
Sometimes when writing an
IF
statement there are times where you want to continue performing additional checks. Thankfully nesting
IF
statements is fairly straightforward, as seen in the following example:
A  B  

1  100 
Even
=IF(ISNUMBER(A1),IF(ISODD(A1),"Odd","Even"),"Not a number")

2  Hello 
Not a number
=IF(ISNUMBER(A1),IF(ISODD(A1),"Odd","Even"),"Not a number")

IF
formulas
In the above examples, I have the same formula in both cell
B1
and
B2
, and they contain multiple
IF
conditions.
The order when using multiple
IF
statements is very important, and the way to read these formulas is no different if you were reading text – you start from the left and progress right.
Therefore, in the example, the first condition checked is determining whether the adjacent cell in column A is a number. The result from this formula returns just
TRUE
or
FALSE
. In our first case in cell
B1
the condition is
TRUE
therefore we will return the value of the second parameter.
The second parameter happens to be another
IF
formula where I check if the same cell is an odd value using the formula
ISODD
, this also just returns
TRUE
or
FALSE
. As the value
100
in cell
A1
is even the condition will return
FALSE
and therefore return the third parameter in its
IF
formula being the string value
"Even"
.
Multiple IF Statement Tips
Reading multiple if statements can get cumbersome once you get beyond having more than 2 nested
IF
statements. Here a couple of things I have done to help make them more manageable:
AND
or
OR
Formulas
If your multiple
IF
statements are returning the same value for certain conditions you may want to see if you can refactor the whole statement to use
AND
or
OR
formulas, like so:
=IF(AND(ISNUMBER(A1),ISODD(A1)),"Odd",)
Insert Line Breaks
You can insert line breaks into your formula which can help you better read your statements. To insert a line break in your code use the
Option/Alt
+
Return
keys on your keyboard.
=IF(ISNUMBER(A1),
IF(ISODD(A1),"Odd","Even"),
"Text")
Break Conditions Into Multiple Ranges
Sometimes it’s just easier to break your
IF
statements up over
multiple ranges
if you have the room on your sheet.
This can help with debugging your formula to check your
IF
conditions at each stage are correct. By splitting the values returned from each
IF
statement into its own range, and then operating on that range with the next
IF
statement it can be easier to spot where things have gone wrong.
Summary
The
IF
formula is one of the most common formulas I use when operating in Google Sheets. It’s a very simple formula that contains three parameters with the first being the condition to check, the second being the value to return if the condition is true, and the third being the value to return if the condition is false.
Besides nesting
IF
formulas based on multiple conditions; check out these
other formulas you can use when you have multiple criteria
.
If you find your
IF
statement formulas are becoming too big, you might want to check out the
SWITCH
statement instead
.