How to Use SWITCH in Google Sheets
=SWITCH(expression, case1, value1, [case2, value2, ...], [default]).
What SWITCH does
Use SWITCH when you test one value against a list of exact possibilities, such as turning a size code into a word. It reads more cleanly than IFS, because you name the value once instead of repeating it in every test.
SWITCH syntax
=SWITCH(expression, case1, value1, [case2, value2, ...], [default])
expression: the value you are testing, usually a cell.case1,value1: the first possible match and the result to return for it.case2, value2, ...(optional): more case and result pairs, checked in order.default(optional): the last argument, returned when no case matches.
A step-by-step example
Here are three size codes in cells A1:A4.
| A | |
|---|---|
| 1 | Size code |
| 2 | S |
| 3 | M |
| 4 | XL |
To turn the code in A3 into a word:
=SWITCH(A3, "S", "Small", "M", "Medium", "L", "Large")
This returns Medium. Sheets compares A3 with each case from left to right, and returns the value next to the first one that equals it.
Add a default
The code in A4 is XL, which is not on the list. Add one extra argument at the end:
=SWITCH(A4, "S", "Small", "M", "Medium", "L", "Large", "Unknown")
This returns Unknown. The default is the single argument left over after the case and value pairs. Google's help page does not say what SWITCH returns when nothing matches and no default is given, so add a default whenever unexpected values are possible.
SWITCH or IFS?
SWITCH only checks whether the expression equals each case. For ranges such as "90 or more", use IFS or IF.
Common SWITCH mistakes
- Using it for comparisons. SWITCH tests equality, not greater-than or less-than.
- An unpaired case. Each case needs a value after it. Only the very last argument can stand alone, as the default.
- Hidden spaces in the source cell, which stop
"M"from matching"M ". Clean it with TRIM. - Missing quotation marks around text.
Try it yourself
Use the size codes above (A1:A4). Type your formula into the box, starting with =, then press Check.
Exercise 1: Code to word
For the code in A3, write a SWITCH that returns Small for S, Medium for M and Large for L.
Exercise 2: Unknown codes
For the code in A4, use the same list but add the default Unknown for any other code.
What to learn next
- IFS: test several conditions in order without nesting.
- IF: run a test and return one of two results.
- IFERROR: replace an error with a clean result.
Syntax and rules in this guide were checked against Google's SWITCH help page. See all guides.