The SWITCH function in Excel
SWITCH checks a value against a list of options, one after another. When it finds a match, it returns the result paired with that option. If nothing matches, it returns an optional default. It replaces long chains of nested IF functions and is easier to read.
SWITCH is available in Excel 365, Excel 2019 and Excel 2021. It won't open correctly in Excel 2016 or earlier.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
| Argument | What it means |
|---|---|
| expression | The value to check, e.g. a status code. Usually a cell reference. |
| value1 | The first option to compare with the expression. |
| result1 | What to return when the expression equals value1. |
| value2, result2, …optional | More option and result pairs. Add as many as you need. |
| defaultoptional | What to return when nothing matches. Without it, a no-match returns #N/A. |
Examples
1. Turn a status code into a label
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Status code | 2 | |||||
| 2 | In progress |
Code 2 matches the second option, so SWITCH returns "In progress". The last argument, "Unknown", is the default.
2. Fall back to the default
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Status code | 7 | |||||
| 2 | Unknown |
7 isn't among the options, so the default is returned. Always add one to avoid #N/A.
3. Grade a score with ranges
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Score | 81 | |||||
| 2 | B |
Put TRUE as the expression and the conditions as the options. The first condition that is true wins: 81 is at least 75, so the result is "B".
Common mistakes
No default value. When nothing matches, SWITCH returns #N/A, which then spreads through other formulas.
Fix: Add a last argument with the default, such as "Unknown" or "".
Conditions in the wrong order. With ranges, the first true condition wins, so the broadest one must come last.
Fix: List the highest threshold first when grading: 90, then 75, then 50.
Text and numbers don't match. A code stored as text, such as "2", is not equal to the number 2.
Fix: Make the options and the expression the same type, or convert with VALUE first.
Frequently asked questions
When should I use SWITCH instead of IF?
Use SWITCH when one value is compared against many fixed options. Use IF for a simple yes/no choice or when conditions are unrelated.
Does SWITCH work in older Excel versions?
Not in Excel 2016 or earlier. Use nested IF or VLOOKUP with a table if the file must open there.
Related functions
- IFIF checks a condition and returns one value if it's true and another if it's false.
- IFSIFS checks several conditions in order and returns the value for the first one that's true: a cleaner alternative to nested IFs.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.