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 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.
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 nestedIF
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 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
! đĄ
Top comments (1)
I was so happy when MS finally put the
SWITCH
function in.I would usually have a sheet full of tables
ListObjects
that stored this kind of information and then use a combination of=IFERROR()
and=VLOOKUP()
to match this data. I find it easier to add, edit and delete the information in the future without fiddling within a function.