Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
Sample Usage
SWITCH(A1:A10, 0, “No”, 1, “Other”)
SWITCH(A3:A8, 4, “Four”, 8, “Eight”)
Syntax
SWITCH(expression, case1, value1, [case2, value2, ...], [default])
-
expression- Any valid values. -
case1- The first case to be checked againstexpression. -
value1- The corresponding value to be returned ifcase1matchesexpression. -
case2, value2, … -Optional: Additional cases and values if the first one doesn’t match the expression. -
default -Optional: An optional value, specified as the last parameter, to be returned if none of the cases match the expression.
See Also
IFS:
Evaluates multiple conditions and returns a value that corresponds to the first true condition.
IF: Returns one value if a logical expression is `TRUE` and another if it is `FALSE`.
Example 1
| A | B | C | |
|---|---|---|---|
| 1 | Data | Result | Formula |
| 2 | 1 | Yes | =SWITCH(A2:A7,0,"No",1,"Yes","Other") |
| 3 | 1 | Yes | |
| 4 | 2 | Other | |
| 5 | 0 | No | |
| 6 | 3 | Other | |
| 7 | 2 | Other |
Example 2
| A | B | C | |
|---|---|---|---|
| 1 | Grade | Passed? | Formula |
| 2 | A | Pass | =SWITCH(A2:A6,"F","Fail","Pass") |
| 3 | B | Pass | |
| 4 | C | Pass | |
| 5 | D | Pass | |
| 6 | F | Fail |