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 in Excel 2013 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 in Excel 2013
In this 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. You can imagine, as the number of options may increase, how complicated it would be to track the function nesting in Excel.
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.