The Excel SWITCH function compares one expression against a list of values and returns the result paired with the first exact match — with an optional default for everything else. Where IFS repeats the cell in every test (A2="N", …, A2="S", …), SWITCH names it once and lists the alternatives. Translating codes, day numbers, and status flags has never been tidier.
>=, no wildcards.
Syntax
| Argument | Description | |
|---|---|---|
expression | Required | The value to compare — a cell, a calculation, or a function result. Evaluated once. |
value1, result1 | Required | If expression exactly equals value1, SWITCH returns result1. |
value2, result2, … | Optional | Up to 126 value/result pairs, checked in order. |
default | Optional | A final unpaired argument returned when no value matches. Omit it and an unmatched expression returns #N/A. |
Available in: Excel 2019 and later, Excel for Microsoft 365, and Excel for the web. Excel 2016 and earlier show #NAME?.
Translate codes to labels
Region codes in A2:A5, full names in B2 copied down. The unmatched “Q” falls through to the default:
| A | B | |
|---|---|---|
| 1 | Code | Region |
| 2 | N | North |
| 3 | S | South |
| 4 | W | West |
| 5 | Q | Unknown |
Text matching is case-insensitive, like most of Excel: "n" and "N" both match. The expression can be a calculation too:
Try it: interactive SWITCH demo
Pick a day number and watch SWITCH hunt for an exact match — then feed it a 9 to see the default earn its keep.
SWITCH vs IFS: exact values vs conditions
The two functions divide the territory cleanly. SWITCH owns exact-value matching — one expression, many candidates:
IFS owns conditions — SWITCH simply cannot express a comparison operator. This logic has no SWITCH equivalent:
Rule of thumb: if every branch starts with the same cell =, use SWITCH; if branches use >=, <, or test different cells, use IFS.
Long lists belong in a table. Past eight or ten pairs, move the code–label pairs onto a sheet and use XLOOKUP instead — easier to edit, and other formulas can reuse the table. SWITCH’s cousin CHOOSE handles the special case where the expression is already a tidy index 1, 2, 3…
Errors & common pitfalls
#N/A — nothing matched and no default. Add a final unpaired argument as the default, or wrap the formula in IFNA.
#NAME? — older Excel. SWITCH needs Excel 2019 or later (or Microsoft 365). For earlier versions, rewrite with nested IF or a lookup table.
Pitfall: trying to use operators. =SWITCH(B2, >=90, "A", …) is a syntax error — SWITCH compares values for equality only. Range logic belongs to IFS.
Pitfall: number vs text mismatch. The number 1 does not match the text "1". If the expression column holds text-formatted digits, quote your values — or convert with VALUE() first.
Pitfall: a default that hides typos. A catch-all like "Other" quietly absorbs misspelled codes. During development, set the default to something loud — "CHECK: " & A2 — so bad inputs surface.
Practice workbook
Frequently asked questions
What's the difference between SWITCH and IFS?
>= — for ranges or multi-cell conditions you need IFS. For pure code-to-label translation, SWITCH is shorter because the expression is written once.How do I add a default value to SWITCH?
=SWITCH(A2, "N", "North", "S", "South", "Unknown"). Because it has no value to pair with, Excel treats it as the default for anything unmatched.Can SWITCH test greater-than or less-than conditions?
Which Excel versions support SWITCH?
Can two values return the same result in SWITCH?
=SWITCH(WEEKDAY(A2,2), 6, "Weekend", 7, "Weekend", "Weekday"). SWITCH has no “6 or 7” shorthand.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class