SWITCH spreadsheet function is a concise way to write what could otherwise be a convoluted
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 SWITCH function demo in Google Sheets.
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
IFs, and maybe some
ORs, 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.
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.
SWITCHcan save you from having to make a complicated series of nested
IFs 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.
SWITCHcan'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).
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 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!), 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.
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.
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.
Ok you’re all set, go flip a