One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula.
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.
Let’s look at a simple example:
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.
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:
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
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
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
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:
|2||Hello||Not a number|
In the above examples, I have the same formula in both cell
B2, and they contain multiple
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
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
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
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:
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
OR formulas, like so:
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
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.
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.
IF formulas based on multiple conditions check out these other formulas you can use when you have multiple criteria.