How Do You Put Multiple Conditions In One Cell In Google Sheets?

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:

1. Conditions 1, 2, 3 and 4 are all true.
2. Conditions 1, 2, 3 are true, but 4 is not true.
3. Conditions 1 and 2 are true, but 3 is not, and 5 is true.
4. Conditions 1 and 2 are true, but 3 and 5 are not.
5. Condition 1 is true, 2 is not, and 6 is true.
6. Condition 1 is true, 2 and 6 are not.
7. Condition 1 is not true, and condition 7 is true.
8. 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:

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:

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.