How do you use the Google Sheets
SWITCH()
formula?
The
SWITCH()
formula in Google Sheets enables you to compress a series of
IF
statements, even nested
IF
statements, into one succinct function.
Take a recent example where I refactored the following formula which helped to add the appropriate years, months or days to an existing date.
Here was the original formula which would calculate the next date according to an increment type:
=DATE(YEAR(A1)+IF(A2="Y",1,IF(A2="A",1,IF(A2="C",100,0))),
MONTH(A1)+IF(A2="Q",3,IF(A2="B",2,IF(A2="M",1,0))),
DAY(A1)+IF(A2="W",7,IF(A2="F",14,IF(A2="D",1,0))))
A  

1  01/01/2022 
2  M 
3 
=DATE(YEAR(A1)+IF(A2="Y",1,IF(A2="A",1,IF(A2="C",100,0))), MONTH(A1)+IF(A2="Q",3,IF(A2="B",2,IF(A2="M",1,0))), DAY(A1)+IF(A2="W",7,IF(A2="F",14,IF(A2="D",1,0))))
02/01/2022 
A1
and type of increment in cell
A2
(in this case
M
= Monthly)
What this formula did was obtain a date value in cell
A1
and then based on a letter inserted in cell
A2
would calculate the next date.
For example, if cell
A1
contained the value
1/01/2022
and the value in cell
A2
contained the value
M
this would add 1 month to the value in cell
A2
and produce
2/01/2022
(1st February 2022).
What Does
SWITCH()
Do?
Looking at the original formula above there are 9 individual
IF
statements. Using a
SWITCH
formula can help to compress those
IF
statements.
For the first series of
IF
statements they can be compressed from:
YEAR(A1)+IF(A2="Y",1,IF(A2="A",1,IF(A2="C",100,0)))
To this:
YEAR(A1)+SWITCH(A2,"Y",1,"A","C",100,0)
Notice the change?
First, the reference to
A2
is the first parameter in the
SWITCH
function. This is what the
SWITCH
function compares with each of the
even parameters
in the remainder of the function. The second parameter contains the value
"Y"
and if the value in
A2
is
"Y"
then the
SWITCH
function returns the next parameter value, being
1
.
If the value in
A2
is
not
"Y"
then the function proceeds to the next even parameter
"A"
. Again, if the value in
A2
is
"A"
then the
SWITCH
function returns the next parameter, being
1
.
Again if the value in
A2
is
not
"A"
then the function proceeds to the next even parameter
"C"
and if the value in
A2
is
"C"
the
SWITCH
function returns the next parameter, being
100
.
If the value in
A2
is
not
"Y"
or
"A"
or
"C"
then you have the option of returning a default value which would be the last
even parameter
in the function. In my example this ended up being the value
0
.
Similarly you can see the same logic being applied to the second series of
IF
statements with second
DATE
parameter and the expression:
MONTH(A1)+IF(A2="Q",3,IF(A2="B",2,IF(A2="M",1,0)))
Which was refactored to the following:
MONTH(A1)+SWITCH(A2,"Q",3,"B",2,"M",1,0)
As you can see the
SWITCH
formula is shorter than the comparative
IF
statements and the
SWITCH
formula would read as follows:

Reference the value in
A2

Is the value in
A2
equal to"Q"
? If so, return3
. If not continue on. 
Is the value in
A2
equal to"B"
? If so, return2
. If not continue on. 
Is the value in
A2
equal to"M"
? If so, return1
. If not continue on. 
Return the value
0
if you end up here.
Then finally the last series of
IF
statements are refactored as follows, from this:
DAY(A1)+IF(A2="W",7,IF(A2="F",14,IF(A2="D",1,0)))
To this:
DAY(A1)+SWITCH(A2,"W",7,"F",14,"D",1,0)
Again the
SWITCH
function is shorter than the comparative
IF
statements and the
SWITCH
function would operate as follows:

Use the value contained in cell
A2

Is the value in
A2
equal to"W"
? If so, return7
. If not, continue on. 
Is the value in
A2
equal to"F"
? If so, return14
. If not, continue on. 
Is the value in
A2
equal to"D"
? If so, return1
. If not, continue on. 
Return the value
0
if there are no matches.
Therefore, the original function has been refactored to the following:
=DATE(YEAR(A1)+SWITCH(A2,"Y",1,"A","C",100,0),
MONTH(A1)+SWITCH(A2,"Q",3,"B",2,"M",1,0),
DAY(A1)+SWITCH(A2,"W",7,"F",14,"D",1,0))
I find this function a lot easier to read and you don’t get caught up with incorrectly inserting the wrong number of closing parentheses with all the
IF
functions you have.
Also, the original formula had 161 characters, whereas this formula has 129 characters – a reduction of almost 20%!
Here’s how this looked in the Google Sheet:
A  

1  01/01/2022 
2  M 
3 
=DATE(YEAR(A1)+SWITCH(A2,"Y",1,"A","C",100,0), MONTH(A1)+SWITCH(A2,"Q",3,"B",2,"M",1,0), DAY(A1)+SWITCH(A2,"W",7,"F",14,"D",1,0))
02/01/2022 
IF
functions to
SWITCH
functions
So when is it best to use the
SWITCH
function over the
IF
function?
You should look at using the
SWITCH
function over a nested set of
IF
functions when you find your
IF
functions are referencing a common cell to return a simple value as this article has demonstrated.
You can read another
SWITCH
example article here where I compare it to the alternative
IFS
function
.
Summary
The
SWITCH
function enables the refactoring of multiple
IF
statements. In the example used in this article the original formula contained 9
IF
functions and these were refactored to 3
SWITCH
functions.
By using fewer
IF
functions, it is easier to read formulas, especially when
IF
functions are nested. Lastly, the refactoring for this example reduced the total number of characters from 161 to 129 – an almost 20% reduction in characters!