The SWITCH guide
SWITCH compares one value with a list of exact matches and returns the result next to the one it equals: =SWITCH(B2, "STD", 5, "EXP", 2, "NXT", 1, "Check code"). The last argument, on its own, is the default.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], …, [default])
Excel works out the expression once, usually just a cell, then goes through the pairs. Up to 126 pairs. If nothing matches and there's no default, the result is #N/A.
The default
The default is a single value at the very end, with no match in front of it. Counting helps: after the expression, the arguments come in pairs, and an odd one out at the end is the default. …, "NXT", 1, "Other", "Check code") has no default: "Other" and "Check code" are read as one more pair.
Exact matches only
SWITCH checks equality. It can't test a range like "90 or more"; use IFS for bands. A common trick is SWITCH(TRUE, B2>=90, "A", B2>=80, "B", "C"), which returns the result of the first test that is TRUE.
Text and numbers are different values: the text "1" doesn't match the number 1. Keep codes in quotes and results as plain numbers when you want to add them up.
SWITCH or a lookup table?
For three or four fixed codes, SWITCH keeps everything in one formula. When the list is longer or changes, a table with XLOOKUP is easier to keep up to date: change a row, not every formula.
Common mistakes
- No default. A blank or mistyped code shows #N/A.
- Codes without quotes.
SWITCH(B2,STD,5)gives #NAME?. - Result before value. Each pair is the match first, then the result.
FAQ
- How does SWITCH work in Excel?
- It compares one value with each match in turn and returns the result paired with the first one that is equal: =SWITCH(B2,"STD",5,"EXP",2,"Check code").
- Why does SWITCH return #N/A?
- The value matches none of the listed values and there is no default. Add a last argument on its own.
- Can SWITCH handle ranges such as greater than?
- Not directly; it only checks equality. Use IFS, or SWITCH(TRUE, test1, result1, …).
- Which Excel versions have SWITCH?
- Excel 2019, 2021, 2024 and Microsoft 365. Older versions show #NAME?.



































































