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  16Dec2021 
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  18Dec2021 
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
.