SWITCH
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. Optionally returns a default value if no match is found.
Syntax
SWITCH(expression, value1, result1, [value_n], [result_n], [default])
Arguments
| Argument |
Description |
Required |
expression |
Value to match against. |
Required |
value1 |
First value to compare with expression. |
Required |
result1 |
Result to return if value1 matches. |
Required |
value_n |
Additional value to compare with expression. |
Optional |
result_n |
Additional result to return if value_n matches. |
Optional |
default |
[OPTIONAL] Value to return if no matches found. If even number of arguments, last argument is treated as default. |
Optional |
Examples
Match with string values
=SWITCH("B", "A", "First", "B", "Second", "C", "Third")
Result ⇒
Second
Match with numbers and default value
=SWITCH(2, 1, "One", 2, "Two", 3, "Three", "Other")
Result ⇒
Two
No match with default value
=SWITCH("X", "A", 1, "B", 2, "Unknown")
Result ⇒
Unknown
Match grade letter
=SWITCH(A1, "A", "Excellent", "B", "Good", "C", "Average", "Needs improvement")
Result ⇒
Excellent
Related Functions
Other Logical functions:
- AND - Returns true if all arguments evaluate to true
- CONTAINS - Returns true if a range contains a specified value
- IF - Returns the first value if logical_expression is TRUE; otherwise returns the second value.
- IFERROR - Returns a custom result when a formula generates an error, and a normal result when no error is detected.
- IFS - Returns the value corresponding to the first TRUE result when evaluating multiple tests.
- ISBETWEEN - Returns true if a provided number is between two other numbers either inclusively or exclusively; otherwise returns false.
- ISBLANK - Returns true if the value is blank, empty, or null; otherwise returns false.
- ISERROR - Returns TRUE if the value is any error value
- ISEVEN - Returns TRUE if the number is even, FALSE if odd
- ISNA - Returns TRUE if the value is the #N/A error value
- ISNUMBER - Returns TRUE if the value is a number
- ISODD - Returns TRUE if the number is odd, FALSE if even
- ISTEXT - Returns TRUE if the value is text
- NOT - Returns FALSE if logical expression is TRUE and TRUE if logical expression is FALSE.
- OR - Returns true if any argument evaluates to true
- XOR - Returns TRUE if an odd number of arguments are TRUE, and FALSE otherwise
← Back to Formula Reference