Hitting the SWITCH in Spreadsheets

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 SWITCH function demo in Google Sheets.

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

SWITCH Benefits

• SWITCH can 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.

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

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.

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
Excel documentation for SWITCH

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