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.
Excel SWITCH - syntax
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:
- Expression is the required argument compared against value1…value126.
- ValueN is a value compared against expression.
- ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
- Default is the value returned if no matches have been found in the valueN expressions. This argument doesn't have a corresponding resultN expression and must be the final argument in the function.
Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
The SWITCH function vs. nested IF in Excel with use cases
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:
- DR - Duplicate Remover
- MTW - Merge Tables Wizard
- CR - Combine Rows.
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.