When to use SWITCH function in Excel

SWITCH function in Excel

The SWITCH function is a highly useful function which can be used for various applications with conditional splitting logic. This is newly introduced to Excel and it is only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

The SWITCH function evaluates an expression with one or more pre-specified values and then returns the corresponding result to the first matching value. If there is no match, then a default value may be returned. This would happen if only a default value is entered in the syntax.

The SWITCH function is somewhat similar to IFS function, where IFS requires conditional expressions. But in Switch you have to specify an expression and a series of values and results. The SWITCH function always search for an exact match. So this function has a unique functionality.

The SWITCH can be used for applications, where you have been before using Nested IF function to perform certain tasks. The SWITCH would save considerable amount of time where complex Nested IF functions are required. Further, it would reduce the complexity of the syntax and you don't need to repeat the expression, therefore the possibility of mistakes are lesser.

Example #1

See the following example, where the Nested IF function has been used. With the IF function, the nesting gets complex with the number of conditions use.

The same thing can also be performed using SWITCH function, without repeating the conditions. The SWITCH function is quite straightforward for this task as it does not get complicated, though more conditions are added.