This tutorial is applicable to Excel 2019 onward.
New in Excel 2019 is the SWITCH function. It looks up a value in a list of values, and returns the result corresponding to the first matching value.
It’s a great alternative to nested IFs, or even the new IFS function.
I liken it to a VLOOKUP, except the ‘table_array’ argument you might be familIar with from VLOOKUP is contained all in one cell.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
Excel SWITCH Function Syntax
=SWITCH(expression, value1, result1, [default or value2, result2], [default or value3, result3],…)
The first argument in SWITCH is “expression”, which will be new to many Excel users, but not to those familiar with SWITCH from other programming languages.
Instead of calling this argument ‘expression’, it could just as easily be ‘value_to_switch’ so don’t feel intimidated by this fancy name.
The value to switch (a.k.a. expression) in this function can be a cell reference, value or formula. Pretty much anything that evaluates to a single value. For example:
| Type of Expression | Example |
| A cell reference | C2 |
| A nested formula | MID(A5,5,3) |
| A structured reference to a Table cell | @[Column1] |
| A math expression | A2+7 |
| A number or text | 10 or “Completed” |
| A named range | Named_Range |
| A logical test | B3="Completed" |
| Boolean Values | TRUE or FALSE |
After the expression argument we have pairs of value and result arguments:
- Value – this is the value you’re looking to replace
- Result – this is what you want to replace ‘value’ with
SWITCH Function Example
We can use SWITCH to return the State details, in column B, for the Cities in column A of this table:
I’ve wrapped each value and result argument onto separate rows in the formula bar in the image above. Here is the formula again without wrapping:
=SWITCH([@City], "Sydney","NSW", "Melbourne","VIC", "Adelaide","SA", "Brisbane","QLD", "Darwin","NT", "Perth","WA", "Not Found")
In English it reads:
If the City, is Sydney, then NSW, if the City is Melbourne, then VIC, if the City is Adelaide, then SA, if the City is Darwin, then NT, if the City is Perth, then WA, and if you don’t find the City name in the list of values then return ‘Not Found’.
Similar Functions to SWITCH
If we compare the SWITCH formula to the equivalent nested IF function:
=IF([@City]="Sydney","NSW", IF([@City]="Melbourne","VIC", IF([@City]="Adelaide","SA", IF([@City]="Brisbane","QLD", IF([@City]="Darwin","NT", IF([@City]="Perth","WA","Not Found"))))))
Or even the new IFS function:
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT",[@City]="Perth","WA",TRUE,"Not Found")
You can see the benefit of SWITCH is that we simply reference the City column once, i.e. we’re not repeating the logical test.
Plus, in comparing it to the alternative of using VLOOKUP, with SWITCH we have the equivalent of VLOOKUP’s table_array in each formula, thus making it somewhat easier to read, but beware the limitations.
Use SWITCH to Return the Fiscal Quarter
In Australia our financial year starts on 1st July and this means we often have to classify dates into fiscal quarters, July being quarter 1 and so on.
One way to do this is with SWITCH:
In the example above I’ve used the MONTH formula in the expression argument. The MONTH function returns the month number, with January being month 1, and so on. I’ve also added a space between each value and result argument to make it easier to read.
Here’s the formula:
=SWITCH(MONTH([@Dates]),1,3, 2,3, 3,3, 4,4, 5,4, 6,4, 7,1, 8,1, 9,1, 10,2, 11,2, 12,2)
In English it reads:
Find the month number in the Dates column, if it’s month 1, it’s quarter 3, if it’s month 2, it’s quarter 3, if it’s month 3, it’s quarter 3, if it’s month 4, it’s quarter 4 and so on.
Limitations
The SWITCH Function is limited to 126 pairs of value and result arguments. However, if your formula uses more than 7 pairs I’d consider an alternative, because this formula will get difficult to maintain and may result in performance issues.
There are more efficient ways to get the same results, as discussed in this tutorial ‘When to say no to Excel Nested IFs’. Even though this article deals with Nested IF’s the desired result is the same, i.e. to lookup a value in a list or table and return a corresponding value.
SWITCH in Earlier Versions of Excel
If a user opens an Excel file containing the SWITCH function in an earlier version of Excel it will still display the result, but the function will be prefixed with _xlfn. Like so:
=_xlfn.SWITCH([@City], "Sydney","NSW", "Melbourne","VIC", "Adelaide","SA", "Brisbane","QLD", "Darwin","NT", "Perth","WA", "Not Found")
They can happily work in the file and save it without breaking the formula.
However, should the user edit the cell in a version of Excel that doesn’t have the SWITCH function the result will be converted to the #NAME? error. Opening it in Excel 2019 again will fix the errors.
I still have no use for it in the ways usually presented as, if nothing else, I simply prefer to achieve things in different ways. Not fascist about it… if a given “you” wishes to use it, or finds it nice, I say go for it. Let a thousand flowers bloom.
I will say it is nice for a long list (long, not l-o-n-g…) especially of items one has to place carefully in an order, or items that need maintained (edit, add, remove type maintainence), or that can go into sublists. Especially with the ability to drag the Formula Editor region down to show more than three lines and placing each item on its own line, or shared with perhaps one other.
But I realized today (“I was Today years old when…”) that it can be used for something that aggravates me all the time: SUBSTITUTE’s inability to do more than one change (“switch”… do we see where this is going?) in a single instance of the function. If you thinking 20 nested IF’s are hard to understand, get right, or maintain, try 20 SUBSTITUTE’s (or 3-4) especially when not all of them are needed acting on any given input, or in which needing to account for 3-4-5 variations on possible input might be the case for each of those 20 things one wishes to change.
A formula like the following (positing the input in cell A1):
=CONCAT( SWITCH(MID(A1,SEQUENCE(1,LEN(A1),1,1),1), “a”,”L”, “r”,”8″, MID(A1,SEQUENCE(1,LEN(A1),1,1),1) ))
… solves that problem in a flash. Or at least as much of a flash as can be. One can put all the variations on a single line in the Formula Editor to group them for maintenance. One can even make sublists for each variation and refer to the sublists. But mostly, instead of wanting, say, three changes and using a single function rather than three nested ones, but ending up with three distinct Spilled strings, one ends up with a single string with all needed changes made.
It is not inherently case sensitive so use EXACT for, say, “R” vs. “r” when that matters. I have not yet worked out how to make it take a range for input and return appropriate results rather than a single run-on result, but there is usually a way to do that with Spilling. One might have to use some convoluted understanding of how the function is looking at things, but there’s normally a way. (Not as reliably as in “Where there’s a whip, there’s a way…
but this isn’t Middle Earth either.) Might find a way, though copying and pasting down a column is no shuddering horror to me.
Never would’ve thought of it (or maybe not for years more, if I live that long) if I hadn’t looked at your SUBSTITUTE page on a whim just before. Yay! Getting senile, with declining mental powers I guess for the connection not to have occurred to me before this. Boo! But… it has a use now that is Roy-Approved!
(Gotta believe the folks coming to Excel from programming languages are gonna have a laugh at this for how it might turn out to be the second way they get taught to use it in programming.)
Nothing wrong with your mental powers, Roy! Nice application of SWITCH with SUBSTITUTE. Thanks for sharing.
I’d assign this to the same bin as CONCATENATE() and 3d pyramid charts
jim
🙂 summed up nicely.
I hate SWITCH() and don’t use it under any circumstance. One might wonder “Why?”
Basically I feel that the cell is no place to maintain a list of, well, anything. Certainly not a list of values to look for and values to seek. I don’t nest IF()’s when I don’t have to, for example, when forced to by the way a formula is setting up. Now with LET() available, I may since I keep “list-y” material at the absolute beginning, not just what one might use with SWITH(), but internal ranges and so on that one might need to change. But SWITCH would not lend itself to that so…
A simple V-, or X-, LOOKUP() solves everything. Completely no need or use for SWITCH().
This is something I believe is in Excel solely because programmers are so used to it and brayed for it though it has no valid use in Excel. A programmer doesn’t get the opportunity to set up a simple lookup table that is maintainable outside his code being edited. They can’t have such things added into databases like a quick table can be added to Excel by the spreadsheet’s author. So SWITCH() in their programming languages is likely supremely valuable. Not in Excel which has a very different approach to things. (Sadly, sometimes.)
You can always tell a programmer with no Excel experience on question sites. “I want to add these two numbers and put the value into cell such-and-such.” Excel goes at it the opposite direction. Instead of declaring variables, assigning values, doing some operation on them, and assigning the result to yet another variable, along with none of those things being updated when one or more change, Excel puts a formula in the resultant cell that goes after the indicated information and does whatever with it. Instead of the writer having to indicate when to refresh each such bit of code’s output and having messages flowing back and forth to support that, the outer program itself the Excel program) sees changes and looks for places which need updating. VERY different approaches and ways of thinking.
(I’m sure I’ve asked programming questions that similarly seem backward and “how do I even start explaining this to him”-like giving away the fact that using Excel is my regular thing and programming is not.)
But nonetheless, while SWITCH in some form must be very useful in programming, it is almost pointless in Excel.
I’m often curious as to whether anyone really does use it much, and if so, for anything outside a few standard uses they picked up from a helpful website, but either never figured out how to extend to other needs, or expanded their fluency with Excel to use much better ways to achieve them, though leaving the learning use lying about ’cause why change it when it’s already done and works?
By the way, now that we have SPILL functionality, I can finally easily use actual cell ranges in functions like SWITCH(). Have to wrap it with IFERROR() and that with TEXTJOIN() (and sometimes VALUE() though not usually) to get a useful result, but it does remove my biggest “no way” reason for never using it, that of the list having to be maintained within the formula.
Considering the city/state example given, who in the world regards having to type out, in doublequotes, six or more pairs of cities and states, no spelling errors, and how to ever maintain over time, as being somehow better and more efficient and clear than entering a cell range for each set of data, or just not using such at all and using V-, or X-, LOOKUP() instead? Consider how that exact example plays out in the US with 50 states. And like in Australia, those states can have more than one city each, even Wyoming and Rhode Island. Not just a nightmare, but there’s that 126 pair limitation. Sadly, “and so on.”
It CAN, like SUMIFS(), benefit from laying out the pairs or conditions on separate rows and each row begun with spaces to line up the pairs of values (or conditions in SUMIFS) within the editor, but that makes the rest of the formula impossible to work with. Six of one, half dozen of the other. In both cases, they belong in tables that are maintained separately. Someone could even write a sheet with a ton of very standard such tables that one could simply keep and copy into a spreadsheet as needed. Quarters for lots of countries and schemes. Month vs. month number, tables of months in various languages, conversions that CONVERT() does not touch and never will. Just sooooo many things. Titled with an appropriate Named Range name. Done once, added to if anyone has a clever thought, otherwise done once for the world, never needed to be done again. (Sigh… if only. If I had wider knowledge of such things about the world, I might try it myself. But I didn’t even know the standard fiscal year in Australia rang mid-year to mid-year (like a lot of governments here in the US) and I have some small familiarity with Australia so that starkly sets up just how unsuited I would be to it. Maybe though. It’d be interesting to learn these things. Hmm… Well, maundering now so… (Anyone wants to do this, I promise, no lawsuits from me about stealing ideas, not even frivolous ones. Also, it seems likely this could be worked into an Add-In, for easier monetizement efforts. Hmm…)
I hate the thing. It aggravates me every time some site gushes about it (which happily does not happen much since it’s old news and MS isn’t pushing it as glorious anymore).
It was truly a pleasure to read that you don’t think it to be so great!!! It truly makes my day. (Whatever that says about me.)
Thanks, Roy! Good to see we’re on the same page 🙂
Is it new in 2019 or 2016?
The MS function page says it was new for 2016
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
Hi Ron,
I think that’s an error because when you click the link to go to the function page it says Excel 2019. I wrote this tutorial back in 2016 and at the time I had the 365 license. When I opened Excel with that license it would say Excel 2016 for Microsoft 365, meaning I had the subscription version of Excel 2016. Those with the perpetual 2016 license never got the SWITCH function. i.e. if you have Excel 2016 now then it must be a perpetual license and won’t have SWITCH. However, since I can’t test that theory I can’t be sure. Hopefully someone with 2016 can confirm/deny this theory 🙂
Mynda
It seems a better approach would be using VLOOKUP with a self-contained multi-dimensional array (The table array exists all in the formula) which will work in all versions of Excel.
Hi Patrick,
I suppose the aim of SWITCH is to make it slightly more user friendly than a complex array formula. Plus, SWITCH is a function used in other programming languages, so some users will already be familiar with its use.
Mynda
Thanks for the great reminder for IFS(). I always tend to forget about this one.