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 ifcase1
matchesexpression
. -
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 |