This article introduces you to the Excel SWITCH function, describes its syntax and provides a couple of use cases to illustrate how you can simplify writing nested IFs in Excel.
If you ever spent far too much time, trying to get a nested IF formula, you'll like using the freshly released SWITCH function in Excel. It can be a real timesaver in situations where complex nested IF is needed. Earlier available only in VBA, SWITCH has been recently added as function in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
Note. Currently, the SWITCH function is available in Excel for Office 365, Excel Online, Excel 2019 and Excel 2016 included with Office 365 subscriptions.
The SWITCH function compares an expression against a list of values and returns the result according to the first matching value. If no match found, it's possible to return a default value which is optional.
The structure of the SWITCH function is as follows:
It has 4 arguments one of which is optional:
Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
The Excel SWITCH function, as well as IF, helps specify a series of conditions. However, with this function you define an expression and a sequence of values and results, not a number of conditional statements. What is good with the SWITCH function is that you don't need to repeat the expression again and again, which sometimes happens in nested IF formulas.
While everything is ok with nesting IFs, there are cases where the numbers of conditions for evaluation make building a nested IF irrational.
To demonstrate this point, let's have a look at the use cases below.
Say, you have several acronyms and you want to return the full names for them:
The SWITCH function in Excel 2016 will be quite straightforward for this task.
With the IF function you need to repeat the expression, so it takes more time to enter and looks longer.
The same can be seen in the following example with the rating system where the Excel SWITCH function looks more compact.
Let's see how SWITCH works in combination with other functions. Suppose, we have a number of dates and want to see at a glance if they refer to today, tomorrow, or yesterday. For this we add the TODAY function that returns the serial number of the current date, and DAYS that returns the number of days between two dates.
You can see that SWITCH works perfectly for this task.
With the IF function, the conversion needs some nesting and gets complex. So the chances of making an error are high.
Being underused and underestimated, Excel SWITCH is a really helpful function that lets you build conditional splitting logic.
Table of contents