Google Sheets SWITCH Formula Example: Refactor IF Functions By 20%

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

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, return 3 . If not continue on.
• Is the value in A2 equal to "B" ? If so, return 2 . If not continue on.
• Is the value in A2 equal to "M" ? If so, return 1 . 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, return 7 . If not, continue on.
• Is the value in A2 equal to "F" ? If so, return 14 . If not, continue on.
• Is the value in A2 equal to "D" ? If so, return 1 . 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

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!