The Excel SWITCH Function

Related Functions:
IF Function
IFS Function

Basic Description

The Excel Switch function compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression. A default value can be supplied, to be returned if none of the supplied values match the test expression.

The syntax of the Switch function is:

SWITCH( expression,   value1,   result1,   [value2,   result2],   [value3,   result3], ..., [default] )

where the arguments are as follows:

expression-The expression or value that is to be compared to each of the supplied values.
value1
[value2], ...
-The values that are to be compared to the supplied expression.
result1
[result2], ...
-The results that are to be returned if the corresponding value matches the supplied expression.
[default]-An optional default value that is to returned if none of the supplied values match the supplied expression.

Note:


Excel Switch Function Example

The following Switch function examples return a person's name corresponding to the subject number that is stored in column A of the spreadsheet. If the number in column A does not match one of the subject numbers 1-3, the function returns the text string "Invalid Subject Number".

Formulas:

 AB
1NumberName
21=SWITCH( A2, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )
33=SWITCH( A3, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )
47=SWITCH( A4, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )

Results:

 AB
1NumberName
21Amy
33Ian
47Invalid Subject Number

For further details and examples of the Excel Switch function, see the Microsoft Office website.


Switch Function Errors

If you get an error from the Excel Switch function, this is likely to be one of the following:

Common Errors
#N/A-Occurs if none of the supplied values match the supplied expression and no default argument has been supplied to the Switch function.
#NAME?-Occurs if you are using an older version of Excel (pre-2016), that does not support the Switch function.