Now Available: SWITCH Function for Excel 2013, 2010, and 2007

Excel 2016 introduced a new function called SWITCH. You can add a SWITCH function to your copy of Excel 2013, 2010, or 2007 with the Excel PowerUps add-in.

The SWITCH function allows you to specify an expression and a series of values and results. Each of the values are compared to the expression and the first one to match has its corresponding result returned.

You can also specify a default value to be returned if none of the values matches the expression.

If you have ever have to fight keeping track of nested IF statements, you’ll definitely appreciate the simplicity of the SWITCH function.

Example of the SWITCH function

This is an example of the SWITCH function in Excel 2013 (or Excel 2010 or 2007). You can see the difference between using SWITCH and accomplishing the same task with nested IF statements. As the number of options increases, keeping tracking of the function nesting Excel becomes more complicated, how complicated.

First, the SWITCH Function in Excel

Let’s say you want to return a hex value based on a color. In the example below, you can think of this as returning the color code associated with the color name.

Next, Using Nested IF Functions Instead

Here is the same function written without the SWITCH function. You can see how unwieldy this would become with more values to keep track of.

Here are some related posts:

TEXTJOIN, SWITCH, IFS, MAXIFS, MINIFS, CONCAT Available for Excel 2013, 2010, 2007 IFS Function in Excel 2016, 2013, 2010 and 2007 Add Excel 2013 Functions to Excel 2010 Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN