The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value.
Syntax
The syntax for the SWITCH function in Microsoft Excel is:
=SWITCH ( expression, value1, result1, value2, result2, ... value_n, result_n [, default] )
Parameters or Arguments
expression
The expression to compare to a list of values.
value1, value2, … value_n
A list of values that are compared to expression. The SWITCH function is looking for the first value that matches the expression.
result1, result2, … result_n
A list of results. The SWITCH function returns the corresponding result when a value matches expression.
default
Optional. It is the default to return if expression does not match any of the values (value1, value2, … value_n).
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:
Results:
Note
- You can enter up to 126 pairs of values and results into the Excel Switch function.
- If you do not supply a default and none of the supplied values match the supplied expression, the Switch function will return the #N/A error.
- The Switch function was introduced in Excel 2019 and so is not available in earlier versions of Excel.
Switch Function Errors
If you get an error from the Excel Switch function, this is likely to be one of the following:
#N/A
Occurs if none of the supplied values match the supplied expression and no default argument has been supplied.
#NAME?
Occurs if you are using an older version of Excel (pre-2019), that does not support the Switch function.