Excel SWITCH Function

This tutorial is applicable to Excel 2016* onwards.

New in Excel 2016 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 IF’s, or even the new IFS function.

I liken it to a VLOOKUP, except the ‘table_array’ argument you might be familar with from VLOOKUP is contained all in one cell.

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 been ‘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:

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 1^st 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 2016 again will fix the errors.

Download the Workbook

*Note: Not all versions of Excel 2016 will have the new functions. If you purchased a standalone version of Excel 2016 then chances are you got a copy before the new functions were added. Excel 2016 Office 365 users will receive new features as they are released. This is part of Microsoft’s strategy to shift more people to the subscription model.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.