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:
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:
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:
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") |
What does the 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:
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") |
What does the SWITCH formula return when no case is satisfied and there’s no default value? It returns #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.