Hitting the SWITCH in Spreadsheets
Tags: spreadsheets
Published: 2019-12-04 21:07:04

---
title: Hitting the SWITCH in Spreadsheets
published: true
description: why and how to use the SWITCH function in Excel, Google Sheets, and Airtable
tags: spreadsheets
cover_image: https://thepracticaldev.s3.amazonaws.com/i/77kr98nbo9gzshqwdhl5.jpg
---

The `SWITCH` spreadsheet function is a concise way to write what could otherwise be a convoluted `IF`/`OR` function. I'll show you two possible uses for `SWITCH` in this post. I know you will find many more uses on your own once you know it 😁

You can follow along with the examples in this post in my <a href="https://docs.google.com/spreadsheets/d/1FnwYVgczrCM0qKEmi-p3CEbf23P02SLZBCvYPyIknpA/edit?usp=sharing">SWITCH function demo in Google Sheets</a>.

### A SWITCH Example

Let’s say you’re running a fitness center with a daily yoga class. There are 3 yoga instructors. They work on this schedule: 

- Tom teaches on Saturday, Sunday, and Thursday
- Alexis teaches on Monday and Friday
- Marianne teaches on Tuesday and Wednesday

How would you automatically show which teacher is teaching the class based on the day of the week in a spreadsheet column?

You could do this with nested `IF`s, and maybe some `OR`s, but that’s complicated and messy, especially if you ever need to update the schedule!

This is where the `SWITCH` function shines. You can write the whole thing inside one set of parentheses, no nesting required. 

Here’s how you could write a `SWITCH` function to automatically show the right instructor for each day of the week: 

`=SWITCH([cell],"Saturday", "Tom", "Sunday", "Tom", "Monday", "Alexis", "Tuesday", "Marianne", "Wednesday", "Marianne", "Thursday", "Tom", "Friday", "Alexis")`

Replace [cell] with the first cell in the column that contains the values you want to switch. In this screenshot I'm staring with C2. 

![SWITCH function inside a Google Sheet](https://thepracticaldev.s3.amazonaws.com/i/dfyfd33cn7hjz68cle9e.png)

I wrote the function in day of the week order, but that’s not required.  What matters is that the possible value from the selected column *goes first*, followed by what you want to show as the result for that value. 

So in this case, "Saturday", which would be in the day of the week column, is followed by "Tom" because I want to show "Tom" in the formula column anytime "Saturday" shows up in the days of the week column.

I couldn't do "Tom" followed by "Saturday" because "Tom" wouldn't be in the days of the week column — or at least, he shouldn't! If this `SWITCH` found a "Tom" in the days of the week column it would throw an error. 

### SWITCH Benefits

* `SWITCH` can save you from having to make a complicated series of nested `IF`s when you have a finite list of possible combinations. 
* It’s case-insensitive on the value, so you don’t have to worry if the stuff in the value column isn’t all in the same case (in fact, that's a good job for a `SWITCH`! You can use it to standardize capitalization in a new column when the text in the value column is in mixed case 😉).
* You can create a “default” value for when you don’t have a match for a value. That's useful for spotting missing data or other unaccounted-for situations in your sheet. I'll show you how to do that in the next example.
* You can use it in Excel, Google Sheets, and Airtable.

### SWITCH Limitations

* `SWITCH` can't do comparisons like greater than/less than on its own.
* It can’t do “fuzzy” matches. Exact spelling matters!
* It’s not available in Numbers for Mac (a huge bummer for me, personally).


### Another Example: SWITCH with a Default Value

Let’s say you’re on a product team where tickets with a specific tag always get assigned to one person. I’m using my 7-person team at [CodePen](https://codepen.io) in this example. 

In this demo we have 10 different possible ticket tags:

Payment, Documentation, Design, Redux, DB, React, CSS, AWS, jQuery, GraphQL, Ruby

And 7 different possible ticket owners:
Tim, Alex, Chris, Marie ([that’s me!](https://twitter.com/mmosley)), Rach, Klare, Stephen

Tim handles: AWS and DB 
Alex handles: Payment 
Chris handles: CSS and jQuery 
Marie handles: Documentation 
Rach handles: GraphQL and Redux 
Klare handles: Design 
Stephen handles: React 

Nobody has been assigned to handle Ruby or DNS tickets yet (and `SWITCH` is going to let us know that!)

Notice that some people have more than one ticket type that should be assigned to them. 


The `SWITCH` would go like this: 

`=SWITCH([cell],"AWS", “Tim","DB","Tim","Payment","Alex","CSS","Chris","jQuery","Chris","Documentation","Marie","Redux", “Rach", “GraphQL","Rach","Design","Klare","React","Stephen","UNASSIGNED")`

Again, be sure to use the actual cell reference in your formula. In this screenshot I'm starting with G2. 

![A SWITCH function with an optional unassigned value](https://thepracticaldev.s3.amazonaws.com/i/u0rtevxmtpeojr03vb24.png)

That last value, `UNASSIGNED`, is the optional default value. It’s displayed when the function encounters a tag that doesn’t have a match in the switch list. That tells us we need to figure out who will take ownership of those tickets. When we decide who that will be, we can edit the function to include the new name. 


### Documentation

[Google Sheets documentation for SWITCH](https://support.google.com/docs/answer/7013690?hl=en)
[Excel documentation for SWITCH](https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)

Ok you’re all set, go flip a `SWITCH`! 💡