Code to label
| A | |
|---|---|
| 1 | Code |
| 2 | W |
Formula: C1: =SWITCH(A2,"E","East","W","West","N","North","Unknown")
Result: West
A2 is compared with each code in turn; the last lone argument is the default.
Logic
SWITCH
Picks a result by exact match.
Syntax
SWITCH(expression, value1, result1, …, [default])Example: =SWITCH(A2,"Boston","East","Denver","Central","Other")
Available in: Excel 2019+
SWITCH compares one expression against a list of values and returns the result for the first match, with an optional default: =SWITCH(expression, value1, result1, …, default). It is cleaner than nested IFs when you test one cell against fixed codes.
When to use it
- You translate codes into labels (E to East, 1 to Monday).
- You compare a single cell against many exact values.
- You want an explicit default instead of #N/A.
- You want to test several conditions by switching on TRUE.
Arguments explained
- expression. The value that is compared with each case, usually a cell reference such as A2. It is evaluated only once.
- value1, result1. The first value to compare with the expression and the result returned when they are equal. The comparison is an exact match.
- value2, result2, …. Additional value and result pairs, up to 126 of them.
- default. Optional last argument, used when nothing matches. It stands alone without a partner value. Without it, an unmatched expression produces #N/A.
Worked examples
Weekend or weekday
| A | |
|---|---|
| 1 | Date |
| 2 | 3/9/2024 |
Formula: C1: =SWITCH(WEEKDAY(A2),1,"Sun",7,"Sat","Weekday")
Result: Sat
WEEKDAY returns 7 for Saturday, so the second pair matches. March 9, 2024 is a Saturday.
Ranges by switching on TRUE
| A | |
|---|---|
| 1 | Units |
| 2 | 75 |
Formula: C1: =SWITCH(TRUE,A2<50,"Low",A2<100,"Mid","High")
Result: Mid
Each test returns TRUE or FALSE; SWITCH picks the first one that equals TRUE.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =SWITCH(C2,"Yes","Bonus","No","None","Other")
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Extra credit | Attempts | Result | |
| 2 | Ann | 92 | Yes | 2 | Bonus | |
| 3 | Bob | 74 | No | 0 | ||
| 4 | Cy | 58 | Yes | 3 | ||
| 5 | Dee | 81 | No | 1 | ||
| 6 | Eli | 66 | 2 |
Result in F2: Bonus
Common errors and how to fix them
- #N/A. No value matched and no default was supplied. Add a final default argument.
- #NAME?. SWITCH exists in Excel 2019 and Microsoft 365 only.
- Text does not match. Matching is exact apart from case. A trailing space in the cell breaks it; use TRIM(A2) as the expression.
- Mis-paired arguments. After the expression, arguments must come as value and result pairs, with one optional default at the end.
Related functions
Frequently asked questions
- When should I use SWITCH instead of IFS?
- Use SWITCH when you compare one value to exact alternatives, and IFS when each branch has its own condition.
- Does SWITCH support ranges like greater than?
- Not directly. Switch on TRUE and write each comparison as a value.
- Is SWITCH case sensitive?
- No.
- What if I have dozens of codes?
- Put them in a two-column table and use XLOOKUP or VLOOKUP instead.