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 |
