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:
Let’s examine each formula individually and how they might fit your needed case.
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
statements. This does start to get messy after a few
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
statements easier to read, but they do provide some assistance by highlighting opening and closing parentheses.
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
Another way of managing multiple criteria using a single formula in a cell is to use the
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
formulas for conditions that can meet multiple criteria and need to have the same result. There shouldn’t be a need to nest multiple
together as you can with the
The only issue with using the
formula is that if a condition
is NOT satisfied
in your formula that an
error is thrown, as demonstrated below:
IFSformula return when no conditions are met? It returns
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.
The third type of formula that allows you to check against multiple criteria without needing multiple cells to do so is by using the
This function operates in the same way as
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
, or a reference to a range containing a value, or range of cells. Then in each
parameter you enter the value you would be expecting and the result you want according to that criteria.
parameter needs to be paired with a corresponding
parameter, with the final parameter being the
returned should no
result is entered as the final parameter then a
or empty cell is produced, as the following demonstrates:
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
formula, however, this does get messy when you begin to have nested
statements and can make reading your formula difficult.
Another good choice is to use
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
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
formula which allows for a default value.
Next, you might want to check how versatile the
function operates in Google Sheets