SWITCH

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

  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  
Was this helpful?
How can we improve it?