Excel functions / Logical
SWITCH
Compares one value against a list of exact matches and returns the matching result.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])Arguments
expression | The value to check, often a cell. |
|---|---|
value, result pairs | Each possible match and what to return for it. |
default | Optional. A final, unpaired result for when nothing matches. |
How it works
SWITCH is the neat choice when you are translating codes: N to North, 1 to Monday, Yes to Present. You write the value once instead of repeating it in every test.
It only matches exact values. For ranges, such as scores of 60 or more, use IFS.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Attended | ||
| 2 | Ana | 72 | Yes | Present | |
| 3 | Ben | 45 | Yes | ||
| 4 | Cal | 88 | No | ||
| 5 |
Result: Present. C2 is Yes, so the result for Yes is returned.
Tips
- Without a default, a value that matches nothing returns #N/A.
Practise SWITCH
Try it yourself in a real spreadsheet, with instant feedback on every answer.