Buzz
In addition to the SWITCH function in Excel, readers can also explore other articles on Mytour to learn about different functions in Excel such as the DVARP function in Excel.
SWITCH Function and Syntax
1. Description of SWITCH Function in Excel
The SWITCH function in Excel compares the supplied value with the provided test expression and returns the corresponding result for the first matching value with the test expression. The default value is returned if no supplied value matches the test expression.
The syntax of the SWITCH function in Excel is:
SWITCH( expression, value1, result1, [value2, result2], [value3, result3], ..., [default] )
The parameters have the following meanings:
- Expression: The expression or value to be compared with each supplied value.
- value1 and [value2], : The values to be compared with the provided expression.
Note:
- You can enter up to 126 pairs of values and results into the SWITCH function in Excel.
- If no default value is provided and no supplied value matches the provided expression, the SWITCH function will return an #N/A error message.
- The SWITCH function was introduced in Excel 2016, so it is not available in earlier versions of Excel. Additionally, the SWITCH function is also not available in Excel 2016 for Mac.
1. An array of SWITCH function examples in Excel
The following SWITCH function example in Excel returns the name of the person corresponding to the number stored in column A of the spreadsheet. If the number in column A does not match any of 1-3, the function will return the text string 'Invalid Subject Number.'
Formula:
Result:
3. Common Errors with Switch Function in Excel
If you encounter errors from the Switch function in Excel, it may be due to:
- #NAME?: Error occurs if no value matches the provided expression and no default argument is provided.
- #NAME?: Error occurs if you are using an older version of Excel (prior to Excel 2016), which does not support the Switch function.
Here, Mytour has provided you with information about the SWITCH function in Excel, one of the basic functions many users rely on. Hopefully, after reading this article, you will have a better understanding of basic functions in Excel.
In addition, you can explore the COUNTIFS function, a conditional statistical function. The COUNTIFS function is commonly used in work tasks and current problems.
