SWITCH
The SWITCH function compares an expression with a list of possible values and returns the corresponding result when there is a match.
This can be useful for creating multi-level conditions when you need to select a result based on several options.
Syntax
SWITCH(expression, value1, result1, [value2, result2, ...], [default])
| Argument | Description | Permitted values |
|---|---|---|
| expression | The value that is compared with the arguments “value1, ... valueN” | Any value |
| value1 | The value with which the “expression” argument is compared | Any value |
| result1 | The value returned if the “valueN” argument matches the “expression” argument | Any value |
| [value2, result2, ...] | (optional) Additional possible values and results | Any value |
| [default] | (optional) The value that the function will return if it does not find any matches. If the argument is not specified, the #N/A error will be returned if there are no matches | Any value |
Examples of use
Selecting a result based on evaluation
=SWITCH(A1, “Excellent”, “5”, “Good”, “4”, “Satisfactory”, “3”, “Unsatisfactory”, ‘2’, “Invalid grade”)
If cell A1 contains the value “Good”, the formula will return “4”. If the value in cell A1 does not match any of the values, the formula will return the text “Invalid grade.”
Using Boolean values as expressions to check a series of conditions
=SWITCH(TRUE, A2>=1000, "High", A2>=500, "Medium", A2>0, "Low", "No data")
In this case, the function will sequentially check each condition and return the result for the first one that is true, or the value “No data.”
Notes
- If the value does not match any of the listed values, the default argument value will be returned, or the #N/A error will be returned.
- The SWITCH function can be useful for simplifying complex formulas and improving readability.
- The maximum number of “value-result” pairs is 126.