# The Excel SWITCH Function

Related Functions:
IF Function
IFS Function

## Function 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:

• 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.

## 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:

A B
1 Number Name
2 1 =SWITCH( A2, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )
3 3 =SWITCH( A3, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )
4 7 =SWITCH( A4, 1, "Amy", 2, "Bob", 3, "Ian", "Invalid Subject Number" )

Results:

A B
1 Number Name
2 1 Amy
3 3 Ian
4 7 Invalid 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. #NAME? - Occurs if you are using an older version of Excel (pre-2019), that does not support the Switch function.