The Excel SWITCH Function

Related Functions:

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.

Return to the Excel Logical Functions Page
Return to the List of All Built-In Excel Functions