Logical
How to Use SWITCH in Excel (Match One Value to Many)
SWITCH compares one expression against a list of values and returns the result that matches the first one it finds. It is the clean way to translate codes, abbreviations, or numbers into readable labels.
Where IFS evaluates a different condition on each line, SWITCH tests the same value over and over, so it is shorter when you map one cell to many possible outcomes. This guide covers the syntax, worked examples, and the optional default.
SWITCH syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default]) | Argument | Required | What it does |
|---|---|---|
expression | Required | The value to compare, e.g. a cell like A2 or an expression like WEEKDAY(A2). |
value1 | Required | The first value to match the expression against. |
result1 | Required | What to return when the expression equals value1. |
value2 / result2 | Optional | More value/result pairs. Add as many as you need. |
default | Optional | A single trailing value returned when nothing matches. Leave it off and an unmatched expression returns #N/A. |
Note: SWITCH tests for an EXACT, equals-only match, so it cannot do ranges like >90. Use IFS for greater-than/less-than logic. Available in Excel 2019, Microsoft 365, and Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Map codes to readable labels
=SWITCH(A2, "A","Active", "C","Closed", "P","Pending", "Unknown") SWITCH compares A2 to each value in turn and returns the matching label. The lone "Unknown" at the end is the default for any code that does not match.
2. Weekday number to a name
=SWITCH(B2, 1,"Sun", 2,"Mon", 3,"Tue", 4,"Wed", 5,"Thu", 6,"Fri", 7,"Sat") Numbers work as match values too. With no default supplied, a value outside 1-7 would return #N/A.
3. Region code with a default fallback
=SWITCH(C2, "NA","North America", "EU","Europe", "APAC","Asia Pacific", "Other") The trailing "Other" has no value paired with it, so SWITCH treats it as the catch-all default.
4. Use an expression as the input
=SWITCH(WEEKDAY(D2), 1,"Sunday", 7,"Saturday", "Weekday") The expression can be a formula. WEEKDAY(D2) returns 1-7; only 1 and 7 are mapped, so every other day falls through to "Weekday".
How to write SWITCH step by step
- 1
Click the result cell and type =SWITCH(
- 2
Select the cell or expression you want to compare, then a comma.
- 3
Type the first value to match, a comma, then the result to return, then a comma.
- 4
Repeat for each value/result pair you need.
- 5
Optionally add one final value with no result as the default.
- 6
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | The expression matched none of the listed values and no default was supplied. | Add a single trailing value as the default, e.g. ..., "Unknown"). |
Ranges do not work | SWITCH only does exact equals matches, so a test like >90 is impossible. | Use IFS or nested IF for greater-than/less-than band logic instead. |
#NAME? | SWITCH is missing in Excel 2016 or earlier. | Use nested IF, or upgrade to Excel 2019 or Microsoft 365. |
Default treated as a value | An even number of arguments after the expression makes Excel pair your intended default with a value. | Make sure the default is the very last argument, with nothing after it. |
Frequently asked questions
What is the difference between SWITCH and IFS?
SWITCH compares one expression against a list of exact values; IFS evaluates a separate logical test on each line. Use SWITCH for equals-only mapping (codes to labels) and IFS when you need ranges like B2>=90.
How do I add a default in SWITCH?
Put a single value at the very end with no result paired after it. SWITCH returns it when nothing else matches, e.g. =SWITCH(A2, "A","Active", "Unknown").
Can SWITCH handle greater-than conditions?
Not directly, because it only matches exact values. For ranges, use IFS or nested IF. A trick is =SWITCH(TRUE, B2>=90,"A", ...), but IFS is clearer for that.
Does SWITCH work in Google Sheets?
Yes, SWITCH works the same in Google Sheets and in Excel 2019 and Microsoft 365. Excel 2016 and earlier do not have it.