Status label
=SWITCH(D2, "O", "Open", "C", "Closed", "P", "Pending", "Unknown") maps codes to words.
What is SWITCH in Google Sheets?
SWITCH evaluates an expression against a list of case values and returns the result for the first match. A default value catches everything else. It cleans up nested IF chains when you compare one cell to many constants like status codes.
When to use it
Use SWITCH for enumerated mappings: region codes to names, numeric ratings to labels, or single-field category translation.
When to skip it
Skip SWITCH when each branch needs different range comparisons. Use IFS for conditions like A1>100.
How it works
-
1
Pass the expression to test, usually a cell reference.
-
2
Alternate case value and result pairs for each allowed match.
-
3
End with a default result if no case matches.
-
4
Keep case values type-consistent with the expression, text vs number.
-
5
Order cases from most specific to general when overlaps could occur.
-
6
Document case list on Settings tab when business adds new codes.
Examples in Google Sheets
Region name
=SWITCH(B2, 1, "East", 2, "West", 3, "Central", "Other") turns numeric region ids into names.
Commission rate
=SWITCH(C2, "Gold", 0.1, "Silver", 0.07, "Bronze", 0.05, 0) assigns rate by tier text.
Build this without starting from a blank cell
Use a Better Sheets tool for SWITCH, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Mixing text and number cases so "1" never matches 1.
- No default argument leaving #N/A on unexpected values.
- Using SWITCH for inequalities like greater than thresholds.
- Duplicate case values where only first wins without notice.
- Huge SWITCH lists better moved to a lookup table with XLOOKUP.
Frequently asked questions
- SWITCH vs IFS?
- SWITCH matches exact values. IFS handles separate logical conditions.
- SWITCH vs nested IF?
- SWITCH is shorter when comparing one expression to many constants.
- SWITCH vs VLOOKUP?
- Large tables belong in lookup ranges. SWITCH suits small inline maps.
- Default value required?
- Optional but recommended to avoid errors on unknown codes.
- SWITCH on arrays?
- Expression can be a range; SWITCH spills results per row in array context.
- Case sensitive?
- Text matches follow normal equality rules.
- SWITCH with expressions?
- First argument can be formula, but cases are still exact value matches.
Related Tutorials
Watch how SWITCH works
Switch Data
How do I reference a different spreadsheet in Apps Script?
Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script
Create Navigation Buttons in Sheets to Other Sheets
Visicalc 2023 Technical Walkthrough
Related blog posts
Guides that explain SWITCH in more depth.
5 Magical Ways To Use the IF() Formula in Google Sheets
5 ways to use the IF function in Google Sheets ISBLANK() Checkbox AND() OR() IF(IF())
Read post →How to Bring value from other cells | How to Reference an array in a cell
Today’s tutorial will cover how to bring a value from other cells. How do you reference an array in a cell? Anika is a member of Better Sheets and she was the one who asked this question.
Read post →What's Better Than Google Sheets?
Nothing is better than Google Sheets. You just need to learn more about the unlimited power of Google Sheets. Functions, Formulas, Apps Script and more.
Read post →Related glossary terms
IFS
IFS runs several condition-result pairs in order and returns the result for the first condition that is TRUE. It replaces long nested IF trees for tiered scoring, bonus bands, and SLA buckets where each step tests a comparison.
Read guide →LET
LET assigns names to calculated values inside one formula, then uses those names in the final expression. Repeated VLOOKUP keys, tax rates, or date bounds appear once, which shrinks formulas and can reduce recalc work on heavy sheets.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →Done reading about SWITCH?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.