The Excel SWITCH function is used to compare one value against a number of other values and then returns the first matching value. If the match is not found, SWITCH takes an optional default value.
Purpose of the SWITCH function
To match multiple values and return the first matched value.
The result that is bound with the first matched value.
- expression – The value to match against
- val1/result1 – First pair of the value and result
- val2/result2 – a Second pair of the value and result [Optional]
- default – Default value that is used when there is no match found. [Optional]
SWITCH function can be used to match a value against multiple possible values and return a result that is similar to the first possible matching value.
In the example below, the formula we entered in D6 is:
Figure 1. Example of the Excel SWITCH Function to Return the Matched Values
We have defined C6 as a rating value -1, 2, 3, etc., and it will return the corresponding result of these values, such as Excellent for 4 and Poor for 1.
The SWITCH function let you test multiple conditions without nesting. The formula normally consists of many conditions that make it easier to handle.
- The SWITCH function is not available in versions up to Excel 2013. It’s available only in Excel 2016.
- 126 values or a pair of results can be handled by the SWITCH function.
- The expression can not only be a value but a formula too.
- In case of no match found, a final argument is entered to return a default result.