There will come a time when using Google Sheets where you will be checking the value of a cell against multiple criteria, which one is the best to use?
There are three handy functions you can use within a single cell to handle multiple conditions:
IF
,
IFS
and
SWITCH
.
Let’s examine each formula individually and how they might fit your needed case.
IF
Formula
The
IF
formula is the simplest and probably one of the first formulas most users of spreadsheets start with. Its a formula that contains three parameters:
=IF(condition, value if condition is true, value if condition is false)
Therefore, if you have multiple criteria to filter through then you would want to start
nesting
your
IF
statements. This does start to get messy after a few
IF
conditions, and begins to looking something like this:
=IF(condition1,
IF(condition2,
IF(condition3,
IF(condition4, refer #1, refer #2)
,
IF(condition5, refer #3, refer #4)
)
,
IF(condition6, refer #5, refer #6)
)
,
IF(condition7, refer #7, refer #8)
)
Here is what each of the reference numbers all mean:
 Conditions 1, 2, 3 and 4 are all true.
 Conditions 1, 2, 3 are true, but 4 is not true.
 Conditions 1 and 2 are true, but 3 is not, and 5 is true.
 Conditions 1 and 2 are true, but 3 and 5 are not.
 Condition 1 is true, 2 is not, and 6 is true.
 Condition 1 is true, 2 and 6 are not.
 Condition 1 is not true, and condition 7 is true.
 Condition 1 and 7 are not true.
As you can see from the example above, nested IF statements with multiple criteria can get very messy and difficult to comprehend!
Spreadsheet programs do make it difficult to be able to properly add spacing to help make your
IF
statements easier to read, but they do provide some assistance by highlighting opening and closing parentheses.
Using nested
IF
statements is a good use case where there are multiple and different outcomes according to the conditions.
Read some tips on how to manage multiple
IF
statements.
IFS
Formula
Another way of managing multiple criteria using a single formula in a cell is to use the
IFS
function.
This function is popular when a cell being referenced has values that infer different results. The function has the following format:
=IFS(condition1, value if true, [condition2, value if true, ...])
What helps make this function more powerful is when you combine your conditions with the
AND
and
OR
formulas for conditions that can meet multiple criteria and need to have the same result. There shouldn’t be a need to nest multiple
IFS
together as you can with the
IF
statement.
The only issue with using the
IFS
formula is that if a condition
is NOT satisfied
in your formula that an
#N/A
error is thrown, as demonstrated below:
A  

1  1 
2 
#N/A
=IFS(A1=2,"Not two",A1=3,"Not three")

IFS
formula return when
no conditions
are met? It returns
#N/A
.
If you want to show an error because of incorrect input, knowing that you have all results covered with your conditions then by all means use this formula. If though you don’t want errors to be thrown, or would prefer there to be a default solution should a result not meet ANY criteria then you would want to use the next formula.
SWITCH
Formula
The third type of formula that allows you to check against multiple criteria without needing multiple cells to do so is by using the
SWITCH
function
.
This function operates in the same way as
IFS
except it allows the user to enter a default value should the criteria not be met.
Here are the parameters of this formula:
=SWITCH(expression, case1, result if case1, [case2, result if case2, ...], [default result])
The first parameter to this formula requires an expression which could be the result of performing an operation, such as
10+10
, or a reference to a range containing a value, or range of cells. Then in each
case
parameter you enter the value you would be expecting and the result you want according to that criteria.
Each
case
parameter needs to be paired with a corresponding
result
parameter, with the final parameter being the
default result
returned should no
cases
be satisfied.
If no
default
result is entered as the final parameter then a
null
or empty cell is produced, as the following demonstrates:
A  

1  1 
2 
#N/A
=SWITCH(A1,2,"Hello World")

3 
Not two
=SWITCH(A1, 2, "Hello World", "Not two")

#N/A
Summary
As the following formulas above have demonstrated there are various ways of handling multiple criteria that can be done from within one cell using an array of formulas.
The most popular choice is using the
IF
formula, however, this does get messy when you begin to have nested
IF
statements and can make reading your formula difficult.
Another good choice is to use
IFS
especially if the condition is to satisfy a range of results, for example if the value of the cell is equal to X then do Y, otherwise if it is greater than Z do this, and if it is less than A then do that.
Finally, there was one caveat we explored with using the
IFS
formula and that is if a condition is not satisfied it will return an error. If this is to be avoided a good alternative is to see if it is possible to use the
SWITCH
formula which allows for a default value.
Next, you might want to check how versatile the
SWITCH
function operates in Google Sheets
.