The `SWITCH()`

function in Google Sheets is handy when dealing with multiple criteria based on a single result.

**The SWITCH() function takes at least 3 parameters, with the first parameter being the value to evaluate, the second and third parameter representing a pair of combined cases and values. There’s also an optional final parameter that acts as the default result if no cases are satisfied.**

Here is a simple demonstration of the SWITCH function:

A | |
---|---|

1 | 16-Dec-2021 |

2 | THU`=SWITCH(WEEKDAY(A1,1),1,"SUN",2,"MON",3,"WED",4,"THU",5,"FRI",6,"SAT")` |

As demonstrated above the `SWITCH`

function in its first parameter contains the `WEEKDAY`

function which takes 2 values: a reference to a date cell `A1`

and the type of result sought (with `1`

being the default type if no value is entered and therefore 1 representing Sunday as 1, Monday as 2, etc).

Depending on the day of the week the `SWITCH`

function then proceeds with evaluating each case statement. The first case statement handles what to do if the result is `1`

and if this matches to return the value `"SUN"`

. As the value of the `WEEKDAY`

function will return `4`

it continues to progress through each case statement until it matches.

With this example, all cases have a corresponding value representing an abbreviated day of the week. If I wanted to change the formula so that `SAT`

and `SUN`

just reported `WEEKEND`

I could change the formula to this instead:

A | |
---|---|

1 | 18-Dec-2021 |

2 | WEEKEND`=SWITCH(WEEKDAY(A1,1),2,"MON",3,"WED",4,"THU",5,"FRI","WEEKEND")` |

As you can see from the above example with 18th December 2021 representing a weekend day, no cases are matched to the result of the first parameter and therefore the default (the final parameter) is returned.

## Why Not Use `IFS`

Instead?

The main differences between the `SWITCH`

function and the `IFS`

function are that the `IFS`

function can evaluate multiple criteria, and it does not provide a default value if NO condition is met, therefore you would need to ensure your formula captures all possible scenarios.

The alternative formula using the `IFS`

formula for the above example would be something like this:

`=IFS(WEEKDAY(A1,2)=1,"MON",WEEKDAY(A1,2)=2,"TUE",WEEKDAY(A1,2)=3,"WED",WEEKDAY(A1,2)=4,"THU",WEEKDAY(A1,2)=5,"FRI",WEEKDAY(A1,2)>5,"WEEKEND")`

As you can see from the above formula it’s very long considering the alternative `SWITCH`

formula! As each condition needs to be evaluated it all would need to contain the `WEEKDAY`

formula to determine the correct value. I changed the second parameter to 2 to make it easier to capture the weekends as Saturday and Sunday would evaluate to greater than 5.

The `SWITCH`

function in comparison evaluates the same expression and only evaluates the result according to each case statement.

## Can A Case Statement Be An Expression?

Unfortunately, the `SWITCH`

function is quite strict on the case statements inside and cannot contain an expression. The case parameter must match a value likely to be returned from the first parameter’s expression.

For example, the following function would produce an error:

`=SWITCH(WEEKDAY(A1,2),1,"MON",2,"TUE",3,"WED",4,"THU",5,"FRI",`**">5"**,"WEEKEND")

Using an expression does not work and will just produce an `#N/A`

result as the case needs to match the result from the first parameter.

If you want to see another example of the `SWITCH`

function check my other post where I reduced my formula code by 20%.

## Summary

The `SWITCH`

function provides an easy way to provide a result based on a single expression. It can be more succinct than the corresponding `IFS`

function, however, does have the drawback that the case statements cannot be expressions themselves and must match actual results.

Next, you might like to explore more about the `IFS`

formula.