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 against expression. -
value1
– The corresponding value to be returned if case1 matches expression. -
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 matches 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 | Mark | Passed? | Formula |
2 | A | Pass | =SWITCH(A2:A6,"F","Fail","Pass") |
3 | B | Pass | |
4 | C | Pass | |
5 | D | Pass | |
6 | F | Fail |