Excel SWITCH Function: Match One Value Against a List
Compares one value against a list of options and returns the matching result.
SWITCH takes one expression and a list of things it might equal, each paired with what to return. Region code to region name, status letter to status word, department number to department label — any time you are mapping one set of values onto another.
What it buys you over IFS is that the thing being tested is written once. An IFS doing the same job repeats the cell reference in every condition, and the day that reference changes you have to find all of them. SWITCH names it at the front and the rest of the formula is just the mapping.
The limit is that SWITCH only tests equality. It cannot ask whether something is greater than a threshold, so numeric bands are not its job — that is IFS, or better, a lookup table. Use SWITCH when the values are discrete and known.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], …, [default])Arguments
expressionRequired- The value being matched. Written once, however many options follow.
value1, result1Required- A thing expression might equal, and what to return when it does. Matching is by equality only.
defaultOptional- A lone final argument with no value paired to it, returned when nothing matched. Without it an unmatched expression gives #N/A.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Attendance | Course |
| 2 | Alice Moreau | 82 | 0.96 | Finance |
| 3 | Bruno Santos | 54 | 0.71 | Marketing |
| 4 | Chen Wei | 91 | 0.88 | Finance |
| 5 | Dana Okafor | 67 | 0.62 | Operations |
Worked examples
=SWITCH(D2, "Finance", "FIN", "Marketing", "MKT", "Unknown")Result: FIN
Course name to code. The lone trailing argument is the default for anything unlisted.
=SWITCH(D4, "Finance", "FIN", "Marketing", "MKT", "Unknown")Result: Unknown
Operations is not in the list, so the default runs.
=SWITCH(D3, "Finance", "FIN", "Marketing", "MKT")Result: MKT
A direct match on the second option. No default needed when one matches.
=SWITCH(TRUE, B2>=90, "A", B2>=80, "B", "Lower")Result: B
The workaround for comparisons: switch on TRUE and make each option a condition. It works, but IFS says the same thing more plainly.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: Nothing matched and no default was supplied.
How to fix it: Add a final lone argument. Unlike IFS, SWITCH has a real default rather than a TRUE trick.
Never matches anything
Why it happens: The values being compared differ invisibly — a trailing space, or a number on one side and text on the other.
How to fix it: TRIM the source, and check the data types agree. SWITCH is not case sensitive but is otherwise exact.
Cannot express a greater-than test
Why it happens: SWITCH only compares for equality by design.
How to fix it: Use IFS for thresholds, or the SWITCH(TRUE, …) form if you want everything in one function.
#NAME?
Why it happens: The Excel version predates SWITCH.
How to fix it: It needs Excel 2019 or Microsoft 365, the same as IFS.
Tips worth knowing
- SWITCH beats IFS when every branch compares the same thing; IFS beats SWITCH when each branch asks a different question.
- Past about six options, move the mapping into a two-column table and use VLOOKUP or XLOOKUP — then it is data someone can edit without touching a formula.
- The default is a single argument at the end, not a pair. That asymmetry is what makes an argument-count error easy to spot.
- SWITCH ignores case, so "finance" and "Finance" match the same option.
Frequently asked questions
What is the difference between SWITCH and IFS?
SWITCH compares one expression against a list of possible values, writing that expression once. IFS evaluates a different condition each time. Use SWITCH for mapping discrete values, IFS for thresholds and mixed conditions.
Can SWITCH handle greater-than comparisons?
Not directly, because it only tests equality. The workaround is =SWITCH(TRUE, A2>90, "A", A2>80, "B", "Other") — switching on TRUE so each option becomes a condition. IFS expresses the same logic more clearly.
How do I set a default value?
Add one extra argument at the very end with nothing paired to it. If the argument count after the expression is even you have pairs only; odd means the last one is the default.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.