One of the most powerful features of Google Sheets is the ability to combine functions or nest functions for advanced data validation in your spreadsheet.
One great example of this is the IF statement.
In Google Sheets, you can nest multiple IF statements together to check your data and return different values depending on the results. This is a very useful skill to learn.
In this tutorial, I will show you how to put multiple IF statements in one cell in Google Sheets.
The IF function evaluates your cells and returns a value if your expression is TRUE or FALSE.
The syntax of the IF statement in Google Sheets is:
IF(logical_expression, value_if_true, value_if_false)
- logical_expression – this means the expression you are evaluating in your formula. You can use any of your standard math and comparison operators to compare numbers or use other cell references in your expression
- value_if_true – this is where you place the value you want to return if your expression returns TRUE. You place this inside quotation marks
- value_if_false – this is optional, if you leave it blank your formula will evaluate to FALSE if the expression is not true. You can also place a value to return inside quotation marks similar to the value_if_true parameter.
Here is a basic example of the way this function can be used.
You can see in the above example I am referencing the cell (A2) to determine if A2 is greater than 5. If this expression evaluates to TRUE my second parameter is set to return “Yes”. If this expression evaluates to FALSE my third parameter is set to return “No”.
Multiple IF Statements
You can combine more than one IF statement together to create a nested IF statement. This will allow you to evaluate multiple expressions and return a different value depending on the result.
The syntax of a nested IF statement is:
IF(logical_expression, value_if_true, IF(logical_expression2, value_if_true2, IF(logical_expression3, value_if_true3, value_if_false)))
With this formula, you can string together multiple IF statements.
The way this formula works is by your first logical expression.
If your logical expression evaluates to true, your formula will return your value_if_true. If your logical expression evaluates to false, the next IF statement will then start to be evaluated.
You can combine together as many IF statements as you want and if none of them return true, it will return your designated value_if_false.
Here is an example of how this can be used in Google Sheets
You can see in the above formula I have 4 IF statements nested together. It checks the first expression and if it evaluates to TRUE it will return my designated value. If the expression evaluates as FALSE it will continue to move on to the next IF statement.
This is just one example of a way that multiple IF statements can be nested together. But with a little creativity, you can use this in your spreadsheet in many different ways.
Using multiple IF statements can get kind of long and complicated, but luckily there is a built-in function in Google Sheets that you can use when you need to nest IF statements.
This called the IFS functions. This function works similar to IF but it allows you to check for multiple conditions.
Here is the syntax of the IFS function:
=IFS(condition1, value1, [condition2, value2, …])
- condition1 – is the expression you are evaluating
- value1 – this is the value that will be returned if your condition evaluates to TRUE
- condition2, value2 – if condition1 evaluates to FALSE your next condition will evaluates and you can place additional conditions and values here
Here is an example of how to use the IFs function:
In the example above I am grading scores. If the first condition is TRUE the formula will stop there, but if the first condition evaluates to FALSE, the formula will move on and begin evaluating my next conditions.
The IFS function makes it much easier to string together multiple IF statements in one cell.
The IF and IFS functions are powerful tools for analyzing and evaluating data in your spreadsheet.
When you are comfortable with these functions, you can begin combining them with other functions, and then the possibilities are endless.
If you’ve mastered these functions, try combining them with other functions like AND, ISBETWEEN, and OR.