• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel SWITCH Function

You are here: Home / Excel Formulas / Excel SWITCH Function
October 18, 2016 by Mynda Treacy

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

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: this is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

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:

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

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Excel Expression Definition
Next Post:Excel IFS Function

Reader Interactions

Comments

  1. jim

    July 2, 2021 at 9:06 pm

    I’d assign this to the same bin as CONCATENATE() and 3d pyramid charts

    jim

    Reply
    • Mynda Treacy

      July 2, 2021 at 10:09 pm

      🙂 summed up nicely.

      Reply
  2. Roy

    July 2, 2021 at 8:07 am

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

    Reply
    • Mynda Treacy

      July 2, 2021 at 11:07 am

      Thanks, Roy! Good to see we’re on the same page 🙂

      Reply
  3. Ron S

    July 2, 2021 at 6:11 am

    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

    Reply
    • Mynda Treacy

      July 2, 2021 at 9:17 am

      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

      Reply
  4. Patrick

    March 27, 2018 at 1:03 am

    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.

    Reply
    • Mynda Treacy

      March 27, 2018 at 9:00 am

      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

      Reply
  5. Daniel Lamarche

    July 28, 2017 at 6:28 am

    Thanks for the great reminder for IFS(). I always tend to forget about this one.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.