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 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

ABC
1DataResultFormula
21Yes=SWITCH(A2:A7,0,“No”,1,“Yes”,“Other”)
31Yes
42Other
50No
63Other
72Other

Example 2

ABC
1GradePassed?Formula
2APass=SWITCH(A2:A6,“F”,“Fail”,“Pass”)
3BPass
4CPass
5DPass
6FFail