=SWITCH(B2,"N","North","S","South","E","East","Unknown") compares B2 with "N", "S" and "E" in turn and returns the name paired with the first exact match. When B2 matches none of them, it returns the last argument, Unknown.
| A | B | C | |
|---|---|---|---|
| 1 | Store | Code | Region |
| 2 | 101 | N | North |
| 3 | 102 | S | South |
| 4 | 103 | W | Unknown |
| 5 | 104 | E | East |
| 6 | 105 | n | North |
Click C2 to see the formula. Store 103 has the code W, which is not in the list, so it gets Unknown. Store 105 has a lower-case n and still gets North: text matching in SWITCH ignores case. Change B4 to S and the region updates.
SWITCH syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
expressionis calculated once: a cell, or a formula such asWEEKDAY(A2)orMONTH(A2).- Then come pairs of a value and the result to return when
expressionequals that value. There can be up to 126 pairs. defaultis optional: an extra argument after the last pair. SWITCH knows it is the default because it has no partner.- The match is exact. SWITCH cannot test "greater than" on its own (see SWITCH(TRUE) below).
SWITCH needs Excel 2019 or later, or Microsoft 365. In a perpetual Excel 2016 or earlier a formula with SWITCH shows #NAME?. Google Sheets has SWITCH too.
The default value, and #N/A without one
Without a default, a value that matches nothing gives #N/A. Here a weekday number from WEEKDAY (1 is Sunday, 7 is Saturday) is turned into a label; column C has no default, column D has "Weekday" as its default:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Day number | No default | With default |
| 2 | 2026-03-14 | 7 | Saturday | Saturday |
| 3 | 2026-03-15 | 1 | Sunday | Sunday |
| 4 | 2026-03-16 | 2 | #N/A | Weekday |
| 5 | 2026-03-17 | 3 | #N/A | Weekday |
| 6 | 2026-03-21 | 7 | Saturday | Saturday |
March 14 and 21, 2026 are Saturdays and March 15 is a Sunday, so both columns name them. The Monday and Tuesday match neither 1 nor 7: column C shows #N/A, column D shows Weekday. Column D also shows that the expression can be a formula: WEEKDAY runs once and SWITCH compares its result with each value.
SWITCH(TRUE, ...) for ranges
SWITCH only checks for equality, but the expression can be TRUE. Each value is then a comparison, and SWITCH returns the result of the first comparison that equals TRUE. That makes it work like IFS, with a default at the end:
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 94 | A |
| 3 | Ben | 81 | B |
| 4 | Chloe | 70 | C |
| 5 | Dan | 65 | F |
| 6 | Eve | 88 | B |
The comparisons are read in order, so the highest threshold goes first, the same rule as for nested IF and IFS. The final "F" has no partner, so it is the default for every score below 70. IFS writes the same rule just as clearly; the only difference is where TRUE goes: once at the start in SWITCH, as the last test in IFS.
Map numbers to names
A frequent use is turning a code number into a word, such as a finishing position into a medal:
| A | B | C | |
|---|---|---|---|
| 1 | Runner | Rank | Medal |
| 2 | Ana | 2 | Silver |
| 3 | Ben | 1 | Gold |
| 4 | Chloe | 4 | |
| 5 | Dan | 3 | Bronze |
| 6 | Eve | 7 |
Chloe and Eve finished outside the top three, so they get the default, empty text. When the codes are 1, 2, 3 and so on without gaps, CHOOSE is shorter, =CHOOSE(B2,"Gold","Silver","Bronze"), but it has no default and returns #VALUE! for 4 and above.
Practice: status codes
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Code | Status |
| 2 | INV-01 | P | |
| 3 | INV-02 | U | |
| 4 | INV-03 | X | |
| 5 | INV-04 | P |
Your turn: In C2, turn the code in B2 into a word with SWITCH: P is "Paid", U is "Unpaid", and anything else is "Check". The formula fills down to C5.
SWITCH vs IFS vs nested IF
All three return one of several results; they differ in what they test.
| SWITCH | IFS | Nested IF | |
|---|---|---|---|
| Tests | One expression for equality | Any condition per pair | Any condition per level |
| Default | Last argument, optional | A final TRUE pair | The last IF's false value |
| No match, no default | #N/A | #N/A | FALSE, if the last IF has no value_if_false |
| Repeats the cell | No: B2 written once | Yes: B2="N", B2="S"... | Yes |
| Excel 2016 and earlier | #NAME? | #NAME? | Works |
Use SWITCH when one cell or one formula is compared with a list of fixed values (codes, weekday numbers, months); it is the shortest and runs the expression only once. Use IFS when each test is a different comparison or reads different cells. Use nested IF when the file must open in Excel 2016 or earlier. When the list of codes is long or changes often, put it in a two-column table and look it up with XLOOKUP instead, so that adding a code means adding a row, not editing every formula.
Frequently Asked Questions
What does the SWITCH function do in Excel?
It compares one expression with a list of values and returns the result paired with the first value that matches exactly. =SWITCH(B2,1,"Gold",2,"Silver",3,"Bronze","") turns a rank into a medal name and returns empty text for any other rank.
Why does SWITCH return #N/A?
The expression matched none of the values and there is no default. Add one more argument after the last pair, such as "Other", and SWITCH returns it instead of #N/A.
Can SWITCH test ranges like greater than?
Not directly, because it only tests for equality. Use TRUE as the expression and comparisons as the values: =SWITCH(TRUE,B2>=90,"A",B2>=80,"B","F") returns the result of the first comparison that is TRUE.
Is there a SWITCH function in Excel 2016?
Only in the Microsoft 365 version of Excel 2016. SWITCH is in Excel 2019, Excel 2021, Excel 2024 and Microsoft 365; a perpetual Excel 2016 or earlier shows #NAME?, so use nested IF or CHOOSE there.
