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, 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 |

`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!