Google Sheets SWITCH Syntax
Copy this directly into your formula bar:
=SWITCH(expression, case1, value1, [case2, value2], [default_value])
Key Takeaway: SWITCH looks for an exact match. If the expression matches Case 1, it returns Value 1.
What Is the Google Spreadsheet Switch Function?
The SWITCH function in Google Sheets determines if a specific expression matches a list of defined cases. If a match is found, it returns the corresponding value.
Think of it as a cleaner, easier-to-read alternative to Nested IF formulas. While a Nested IF requires you to repeat your logic over and over (e.g., IF(A1="Red", 1, IF(A1="Blue", 2...))), SWITCH allows you to define the target cell once and simply list the outcomes.
Google Sheets SWITCH Syntax
The function requires at least three parameters to operate effectively:
- exp (Expression): The specific cell or formula you want to test (e.g., Cell A2).
- case1: The first scenario to look for (e.g., “Red”).
- val1 (Value): The result to show if Case 1 is true (e.g., “Stop”).
- [default]: (Optional) The value to return if none of the cases match. Highly recommended to avoid #N/A errors.
The Simplest Example (Hardcoded)
Before using cell references, it helps to see how the logic works with simple text. Imagine you want to convert numbers 1, 2, and 3 into “Gold”, “Silver”, and “Bronze”.
The Formula:
=SWITCH(A2, 1, "Gold", 2, "Silver", 3, "Bronze", "No Medal")In this example:
- Google Sheets looks at cell A2.
- If A2 is 1, it displays “Gold”.
- If A2 is 4 (or anything else), it displays the default: “No Medal”.
Real-World Example: Using Cell References
In most dashboards, you won’t type “Gold” manually; you will reference a “Key” or “Legend” elsewhere in your sheet. This makes your formulas dynamicโif you update the key, all formulas update automatically. We can explore this in the following example.
The Scenario
We have student ratings (1-5) in column B, and a Grade Key (J2:K6). We want to match the rating to the grade.
Step-by-Step Guide
- Start the SWITCH Formula: Click cell C2 and type
=SWITCH(. - Select the Expression: Click cell B2 (the rating).
- Map the Cases: Select the first case in your key (J2), then the value (K2). Repeat for all 5 cases.
- Lock Your References (Crucial Step): If you drag this formula down, the references to your Key (J2:K6) will move, breaking the formula. You must use Absolute References ($) for the Key.
Correct Formula for Autofill:
=SWITCH(B2, $J$2, $K$2, $J$3, $K$3, $J$4, $K$4, $J$5, $K$5, $J$6, $K$6, "Invalid Rating")
SWITCH vs. IF vs. IFS: Which Should You Use?
Many users are confused about when to swap IF for SWITCH. Use this table to decide the best tool for your specific task.
| Function | Best Used For | Key Limitation |
|---|---|---|
| SWITCH | Exact Matches. Ideal for lists, statuses (Open, Closed, Pending), or converting IDs to Names. |
Cannot easily handle mathematical ranges (e.g., “Greater than 10”) without complex workarounds. |
| IFS | Multiple Different Conditions. Good for ranges (e.g., Score > 90 is A, Score > 80 is B). |
Does not support a “Default” value natively (you must add TRUE, "Default" at the end). |
| IF (Nested) | Simple Binary Logic. Best for simple True/False scenarios. |
Becomes unreadable and prone to errors when nested more than 3 times. |
Wait, can SWITCH do “Greater Than”?
No, not easily. This is the most common mistake users make.
SWITCH is designed for 1-to-1 matching (e.g., Does Cell A1 equal exactly “Red”?). If you need to check if Cell A1 is greater than 100, use the IFS function instead.
Frequently Asked Questions
What happens if no case matches the expression?
If no case matches and you have NOT provided a default value, Google Sheets will return a #N/A error. It is best practice to always add a final argument to your formula (e.g., “Unknown”) to handle these errors gracefully.
Can I use SWITCH with text and numbers?
Yes. SWITCH can handle both. However, the data type in the expression must match the case. If your cell contains the number 10, but your case is written as text “10”, SWITCH will not find a match.
Wrapping Up
The SWITCH function is a powerful tool for cleaning up your spreadsheets and avoiding “spaghetti code” created by nested IFs. Remember: Use SWITCH for exact lists of items, and stick to IFS for mathematical ranges.