Logical
SWITCH
Definition
SWITCH returns the result that matches a value, or a default when none match.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], ...)
Arguments
| Argument | Meaning |
|---|---|
expression |
The value to match, or the cell holding it. |
value1 |
The first value to compare with the expression. |
result1 |
What to return when the expression equals that first value. Text must sit in double quotes. |
value2, result2, ... |
Optional extra pairs. An unpaired last argument is the default if nothing matches. |
Worked example
A compass sheet maps a letter to a full name. Letters are in A2:A4.
| A: Code |
|---|
| S |
| N |
| E |
Formula: =SWITCH(A2,"N","North","E","East","S","South")
Result: South. A2 is S, which matches the third pair. Filled down, the formula gives South, North, and East.
Practice
Cell A2 holds 2. In B2, write a formula that returns "one" when A2 is 1, "two" when A2 is 2, and "three" when A2 is 3.
- In B2, write a SWITCH formula that shows one when A2 is 1, two when A2 is 2, and three when A2 is 3.
- B2 should show the result for 2.
Step 1 of 2
Enter =SWITCH(A2,1,"one",2,"two",3,"three") in B2.
A2 is 2, so the result is two.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses SWITCH.
B2 now shows two.
Related functions
IF and IFS test whether a comparison is true. SWITCH matches an exact value and returns the paired result.
Common mistake
Writing a comparison such as A2>="N" as a SWITCH match: SWITCH only accepts exact values, so inequalities belong in IFS or IF.