What Is the SWITCH Function?
The SWITCH function tests an expression against a list of cases and returns the value paired with the first matching case. It works like a dictionary lookup: you give it a key, and it hands back the corresponding value. When you have a single cell to compare against several exact values, SWITCH is almost always cleaner than chaining IF or IFS.
Syntax
=SWITCH(expression, case1, value1, [case2, value2, ...], [default])
| Parameter | Description |
|---|---|
| expression | The value or cell reference to evaluate. |
| case1 | The first value to compare against the expression. |
| value1 | The result returned if expression matches case1. |
| case2, value2, ... | Additional case-value pairs (optional). |
| default | A fallback value returned if no case matches (optional). If omitted and nothing matches, the formula returns #N/A. |
The default value is a single argument at the end, not a case-value pair. This is a common point of confusion.
Basic Examples
Converting Department Codes to Names
| A | B (Formula) | B (Result) | |
|---|---|---|---|
| 1 | Code | Department | Department |
| 2 | ENG | =SWITCH(A2,"ENG","Engineering","MKT","Marketing","FIN","Finance","Unknown") | Engineering |
| 3 | MKT | =SWITCH(A3,"ENG","Engineering","MKT","Marketing","FIN","Finance","Unknown") | Marketing |
| 4 | OPS | =SWITCH(A4,"ENG","Engineering","MKT","Marketing","FIN","Finance","Unknown") | Unknown |
Mapping Numeric Ratings to Labels
| A | B (Formula) | B (Result) | |
|---|---|---|---|
| 1 | Rating | Label | Label |
| 2 | 5 | =SWITCH(A2,5,"Excellent",4,"Good",3,"Average",2,"Poor",1,"Terrible","N/A") | Excellent |
| 3 | 3 | =SWITCH(A3,5,"Excellent",4,"Good",3,"Average",2,"Poor",1,"Terrible","N/A") | Average |
Assigning Pricing Tiers
=SWITCH(B2, "Basic", 9.99, "Pro", 29.99, "Enterprise", 99.99, 0)
If B2 contains a plan name, this returns the monthly price. The trailing 0 is the default for unrecognized plans.
Advanced Examples
SWITCH with Calculated Expressions
You can pass a formula as the expression, not just a cell reference:
=SWITCH(WEEKDAY(A2,2),
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
"Weekend"
)
This converts a date in A2 into a weekday name. WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday, and since Saturday and Sunday have no explicit cases they fall through to the default "Weekend."
Returning Formulas from SWITCH
SWITCH can return calculated results, not just static text:
=SWITCH(B2,
"Flat", C2 * 0.05,
"Tiered", IFS(C2>=10000, C2*0.08, C2>=5000, C2*0.06, TRUE, C2*0.04),
"None", 0,
0
)
Here the commission model in B2 determines which calculation runs against the sales amount in C2.
Common Mistakes
- Confusing SWITCH with IFS. SWITCH checks for exact matches against a single expression. IFS evaluates independent TRUE/FALSE conditions. If you need range-based logic (greater than, less than), use IFS instead.
- Misplacing the default value. The default is a lone argument at the very end. If you accidentally pair it with a case, your results will shift and the formula may error out.
- Case sensitivity. SWITCH is not case-sensitive by default in Google Sheets, so "eng" matches "ENG". This is usually fine, but worth knowing if you expect strict matching.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.