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.
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.
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
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
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
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:
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
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.
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.
default result is entered as the final parameter then a
null 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
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.