SheetsdrillSWITCH
SWITCH(expression, case1, value1, [case2, value2, ...], [default]) compares an expression against each case in turn and returns the value paired with the first exact match, or the default when nothing matches. It is cleaner than nested IF when you are mapping codes to labels.
Syntax
=SWITCH(expression, case1, value1, [case2, value2, ...], [default])| Part | What it is |
|---|---|
expression | The cell or calculation to compare, for example A2. |
case1, value1 | A value to compare against and what to return when it matches. Matching is exact (case insensitive for text). |
default | Optional last argument returned when no case matches; without it you get #N/A. |
Examples
=SWITCH(A2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")Result: North for N, West for W, Unknown for anything else
=SWITCH(WEEKDAY(A2), 1, "Weekend", 7, "Weekend", "Weekday")Result: Weekend for Sunday (1) and Saturday (7), otherwise Weekday
=SWITCH(TRUE, B2>=90, "A", B2>=80, "B", "C")Result: A for 95, B for 85, C for 60
The TRUE trick turns SWITCH into an IFS; IFS itself is clearer.
Common mistakes
- #N/A because no case matched and there is no default; add a final single argument.
- SWITCH tests equality only, so SWITCH(B2, ">90", ...) never matches; use IFS for comparisons.
- An odd number of arguments after the expression is read as having a default; if you get surprising results, check that each case has a value.
Practice SWITCH
These drills check your answer on live data, free, no account needed.
- Region names with SWITCHTurn the one-letter code into a region name: N is North, S is South, E is East, W is West. Any other code shows "Unknown". Fill C2 and C3 using SWITCH.
Questions
SWITCH or IFS, which should I use?
SWITCH when one value is compared against fixed options; IFS when each test is a different comparison such as greater than.
Can SWITCH return a range or formula?
Yes, each value can be any expression, for example =SWITCH(A2, "USD", B2, "EUR", B2*1.08, B2).
Related
- IFSHow the IFS function in Google Sheets checks several conditions in order and returns the first match, with examples for grades, tiers and bands, plus errors.
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- AND and ORHow AND and OR work in Google Sheets to test several conditions at once, how to use them in IF, and why they fail inside FILTER and ARRAYFORMULA, with fixes.