SWITCH function
SWITCH compares one expression against a list of exact values and returns the result for the first match, or a default. It is cleaner than nested IFs when testing one value for equality.
Example
Read it as a sentence: check F1 against 0 and show zero if it matches, otherwise check for an empty text and show empty, which gives zero.
Syntax
SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Arguments in [square brackets] are optional.
-
expression(required) - The value to compare.
-
value1(required) - First value to match against.
-
result1(required) - Result if expression equals value1.
-
value2, result2(optional) - More match and result pairs.
-
default(optional) - Fallback if nothing matches; a lone final argument.
Returns: The result for the first matching value, or the default.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SWITCH(2,1,"a",2,"b") | b | Match |
=SWITCH(3,1,"a",2,"b","z") | z | Default |
=SWITCH("2",2,"num","2","txt") | txt | Text vs number |
=SWITCH({1,2},1,"a",2,"b") | a | Array spills |
=SWITCH("A","a","lower") | lower | Case-insensitive? |
=SWITCH(Data!F1,0,"zero","","empty") | zero | Blank value |
=SWITCH(3,1,"a",2,"b") | #N/A | No match |
=SWITCH(Data!F5,1,"a","z") | #DIV/0! | Error value |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Only exact equality; it cannot test ranges like >10.
- Without a default and no match you get #N/A.
- An odd trailing argument is treated as the default.
Where it works
- Excel
- SWITCH arrived in Excel 2019. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .