The Microsoft Excel SWITCH function evaluates multiple values and returns the first match. In other words, the given expression is evaluated against a list of values, and returns the result corresponding to the first matching value. However, an optional default value may be returned if there is no match.
Moreover, you can evaluate up to 126 matching values and results. In addition, please note that if there is no match and no else argument are determined, the SWITCH function will return an error. Therefore, it is very important to determine a default value to avoid any possible errors.
Excel SWITCH function Syntax
|
1 |
=SWITCH(expression, value1, result1, [default_or_value2, result2], ...) |
Arguments
- EXPRESSION – The value or expression to match against.
- VAL1/RESULT1 – The first value and its related result.
- VAL2/RESULT2 – [optional] The second value and its related result.
- DEFAULT – [optional] The default value to use when no match is found.
Example
|
1 |
=SWITCH(A2,1,"Outstanding",2,"Exceed Expectation",3,"Meet Expectation",4,"Need Improvement",5,"Unsatisfactory","Undetermined") |
As a result, the above Microsoft Excel Switch Function returns the following results based on the given evaluations:
| Evaluation | Result |
|---|---|
| 1 | Outstanding |
| 2 | Exceed Expectation |
| 3 | Meet Expectation |
| 4 | Needs Improvement |
| 5 | Unsatisfactory |
| 6 | Undetermined |
Lastly, please note that the excel switch function performs only the exact match. So, you can not include logical operators like the greater than sign (>) or the less than sign (<) in the expression of the switch function to determine a match. However, you can use the IF function which allows you to make logical comparisons between a value and what you expect.
In addition, please refer to Microsoft Documentation about the Switch Function for more details.
For more Excel functions, please refer to our excel posts.