Excel SWITCH Function

Using the Excel SWITCH Function


The SWITCH function compares one value, called an expression, against a list of values and returns a user specified result for the first matching value. If no matching values are found, you may specify an optional default value to be returned. The SWITCH function is new as of Excel 2016.


=SWITCH (expression, value1, result1, [default or value2, result2], ...)

Syntax Breakdown

The expression is required and is a value that will be compared against another value (i.e. value1, value2...valueN). The expression can be hardcoded, reference another cell, or be the output of another function or formula.

value1...valueN The valueN argument is the value that will be compared to the expression. This is required.

resultN The resultN argument is required and is the value that is returned if the valueN argument matches the expression. Each valueN argument must have a corresponding resultN.

default In the event that no matches are found for the valueN expression the default value will be returned. Default must be the final argument in the SWITCH function.

Usage Notes

The SWITCH function is used to compare one value against a list and return a user specified value. The function can accept up to 254 arguments, which would mean 126 pairs of values and results for SWITCH.

Using SWITCH allows you to compare multiple conditions at once without having to use nested IF statements, similar to the IFS function.

SWITCH will return the #N/A error if no matching values are found and no default value is provided.

SWITCH #N/A! Error