How to Use the SWITCH Function in Excel – Step‑by‑Step Guide
What is the SWITCH Function?
The SWITCH function evaluates an expression against a list of values and returns the result corresponding to the first match. It replaces long chains of nested IF statements with a cleaner, more readable formula. SWITCH is available in Excel 2019 and later (including Microsoft 365), Excel Online, and Google Sheets (identical syntax).
Syntax and Parameters
=SWITCH(expression, value1, result1, [value2, result2, …], [default])
| Parameter | Description |
|---|---|
| expression | The value, cell reference, or formula to test. |
| value1, value2, … | Values to compare against expression, evaluated in order. |
| result1, result2, … | Value returned when the corresponding value matches expression. |
| default (optional) | Value returned when no match is found. If omitted, SWITCH returns #N/A. |
Basic Example: Text Match
Sample data
| A (Code) | B (Formula) | C (Result) |
|---|---|---|
| A | =SWITCH(A2,"A","Approved","B","Pending","C","Rejected","Unknown") |
Approved |
| B | =SWITCH(A3,"A","Approved","B","Pending","C","Rejected","Unknown") |
Pending |
| C | =SWITCH(A4,"A","Approved","B","Pending","C","Rejected","Unknown") |
Rejected |
| D | =SWITCH(A5,"A","Approved","B","Pending","C","Rejected","Unknown") |
Unknown |
Formula (copy down)
=SWITCH(A2,"A","Approved","B","Pending","C","Rejected","Unknown")
The expression A2 is tested against each value in sequence. The first match returns its paired result; "Unknown" is the default for unmatched codes.
Using SWITCH with Numbers and Dates
SWITCH works with any directly comparable data type—text, numbers, or dates. The expression and values must share the same data type; Excel does not coerce text "1" to number 1.
Sample data
| A (Expression) | B (Formula) | C (Result) |
|---|---|---|
| 1 | =SWITCH(A2,1,"One",2,"Two",3,"Three","Other") |
One |
| 2 | =SWITCH(A3,1,"One",2,"Two",3,"Three","Other") |
Two |
| 3 | =SWITCH(A4,1,"One",2,"Two",3,"Three","Other") |
Three |
| 5 | =SWITCH(A6,1,"One",2,"Two",3,"Three","Other") |
Other |
Tips for numeric and date comparisons
| Tip | Why it matters | Example |
|---|---|---|
| Keep data types consistent | Text "1" ≠ number 1 in SWITCH | SWITCH(A2,1,"One") returns #N/A if A2 contains "1" |
Use DATE() or TODAY() for dates |
Date literals may be read as text | SWITCH(A2,DATE(2024,1,1),"New Year") |
| Round calculated values | Hidden decimals break exact matches | SWITCH(ROUND(A2,0),1,"One",…) |
| Always provide a default | Prevents #N/A from breaking downstream formulas | Add "Other" as final argument |
Handling Errors with a Default Value
Without a default argument, SWITCH returns #N/A when no match exists. This error propagates into SUM, AVERAGE, charts, and conditional formatting. Always include a default value as the last argument.
Example – grading scores with SWITCH(TRUE,…)
| A (Score) | B (Formula) | C (Result) |
|---|---|---|
| 95 | =SWITCH(TRUE,A2>=90,"A",A2>=80,"B",A2>=70,"C","Incomplete") |
A |
| 82 | =SWITCH(TRUE,A3>=90,"A",A3>=80,"B",A3>=70,"C","Incomplete") |
B |
| 67 | =SWITCH(TRUE,A4>=90,"A",A4>=80,"B",A4>=70,"C","Incomplete") |
Incomplete |
| 45 | =SWITCH(TRUE,A5>=90,"A",A5>=80,"B",A5>=70,"C","Incomplete") |
Incomplete |
SWITCH(TRUE, …) lets you test thresholds; the first true condition wins.
Compatibility and Performance
| Issue | Excel | Google Sheets | Recommendation |
|---|---|---|---|
| Availability | Excel 2019/365+ | All current versions | For Excel 2016 or earlier, use nested IF, CHOOSE or a lookup table |
| Case sensitivity | Case-insensitive | Case-insensitive | Use EXACT inside SWITCH(TRUE,…) for case-sensitive matches |
| Error handling | #N/A without default | #N/A without default | Always supply a default value |
| Array formulas | SWITCH returns one value per call | Wrap in ARRAYFORMULA or MAP |
Excel: =MAP(A2:A4,LAMBDA(x,SWITCH(x,"Red",1,"Blue",2,0))) |
| Performance | Sequential evaluation | Sequential evaluation | >20 cases: prefer VLOOKUP/XLOOKUP or a reference table |
Performance tips 1. Keep the case list short—SWITCH evaluates sequentially. 2. Avoid volatile functions (NOW, RAND, INDIRECT) inside the expression. 3. For large datasets, a helper table with XLOOKUP/VLOOKUP calculates faster than a 50-branch SWITCH.
FAQ
Can SWITCH replace nested IF statements?
Yes. SWITCH is cleaner and faster for exact-match scenarios with a fixed set of values. It cannot handle range conditions (e.g., "score ≥ 90") directly—use SWITCH(TRUE, A2>=90,"A", A2>=80,"B", …) for thresholds.
Which Excel versions support SWITCH?
Excel 2019, Excel 2021, and Microsoft 365. Excel 2016 and earlier do not have SWITCH; use nested IF, CHOOSE, or a lookup table instead.
How does SWITCH differ from CHOOSE?
CHOOSE selects by index number (1, 2, 3…) rather than by matching a value. Use CHOOSE when the expression is already a sequential integer; use SWITCH when you need to match specific values like text codes or non-sequential numbers.
What should I do if SWITCH returns an error?
Add a default value as the final argument to prevent #N/A. If you still see errors, verify that the expression and value arguments share the same data type (text vs. number vs. date) and that no hidden spaces or rounding differences exist.