Home › Excel functions › SWITCH
SWITCH Function in Excel: Syntax and Examples
SWITCH compares one value with a list of options and returns the result for the option that matches. It's ideal for turning codes into names: "NL" into Netherlands, 1 into Monday, "P" into Paid.
SWITCH · In this guide
SWITCH syntax
=SWITCH(expression, value1, result1, [value2, result2], …, [default])| Argument | What it means |
|---|---|
| expression | The value to check, usually a cell. |
| value / result | Pairs: if the expression equals this value, return this result. |
| default (optional) | A last, unpaired value returned when nothing matches. Without it you get #N/A. |
SWITCH is available in Excel 2019, 2021, 2024 and Microsoft 365.
Basic example: status codes
| A | B | |
|---|---|---|
| 1 | Code | Status |
| 2 | P | Paid |
| 3 | O | Open |
| 4 | C | Cancelled |
| 5 | X | Unknown |
"P" matches the first value, so B2 shows Paid. "X" matches nothing and gets the default: Unknown.
Numbers to names: weekday names
=SWITCH(WEEKDAY(A2, 2), 6, "Weekend", 7, "Weekend", "Weekday")WEEKDAY with type 2 returns 1 for Monday through 7 for Sunday. Saturday (6) and Sunday (7) return "Weekend"; anything else "Weekday".
The SWITCH(TRUE) trick for ranges
SWITCH only checks for equal values, so it can't do "greater than" directly. A common trick is to switch on TRUE and use conditions as the values:
=SWITCH(TRUE, B2>=80, "A", B2>=70, "B", B2>=60, "C", "F")This works, but IFS is usually clearer for ranges.
SWITCH vs. IFS vs. a lookup table
- SWITCH: one value, compared to a handful of fixed options. Short and readable.
- IFS: different conditions or comparisons like >= and <.
- A lookup table with XLOOKUP: more than about 5 options, or options that change. Easier to maintain than a long formula.
Frequently asked questions
Why does SWITCH return #N/A?
No value matched and there's no default. Add a final, unpaired argument as the default.
Is SWITCH case-sensitive?
No. "p" and "P" match the same value.
Can SWITCH match multiple values to one result?
You have to repeat the result for each value, as in the weekend example. For many values, a lookup table is simpler.