SWITCH function
SWITCH compares one value against a list of exact matches and returns the result paired with the first match, with an optional default at the end. It is the cleanest way to turn codes into labels or numbers, such as shipping codes into delivery times, without a chain of IFs.
- Available in
- Excel 2019 and later, including Microsoft 365
- Category
- Logical functions
- Returns
- The result for the first matching value, the default, or #N/A.
- Google Sheets
- Available in Google Sheets.
Syntax
=SWITCH(expression, value1, result1, ..., [default])
| Argument | Required? | What it is |
|---|---|---|
expression | Required | The value to compare, usually a cell. |
value1 | Required | The first value to match against. |
result1 | Required | What to return when expression equals value1. Add more value and result pairs as needed. |
default | Optional | What to return when nothing matches. Left out, SWITCH returns #N/A. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =SWITCH(B2,"EXP","Express","STD","Standard","Unknown") | Express | A code turned into a label |
| D3 | =SWITCH(B3,"EXP",2,"STD",5,"PU",0) | 5 | The same code turned into delivery days |
| D4 | =SWITCH(B4,"EXP","Express","STD","Standard") | #N/A | PU has no match and there is no default, so it returns the #N/A error |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order | Ship code | Result | |
| O-310 | EXP | Express | |
| O-311 | STD | 5 | |
| O-312 | PU | #N/A |
Practice SWITCH
Graded instantly, then re-checked on changed data.
Response times by priority
C2, return the response time for each ticket: P1 is 1 hour, P2 is 4, P3 is 24, and any other code is 72. Use SWITCH, then fill down to C6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A | No value matched and there is no default. Add a final argument, such as "Other". |
| Trying to match ranges like >100 | SWITCH only tests exact equality. For thresholds use IFS, or start with TRUE: =SWITCH(TRUE,B2>100,"High",B2>50,"Medium","Low"). |
| #NAME? | SWITCH needs Excel 2019 or later. Use nested IFs or a lookup table in older versions. |
Tips
- For more than five or six codes, a two-column table with XLOOKUP is easier to update than a long SWITCH.
- =SWITCH(WEEKDAY(A2),1,"Weekend",7,"Weekend","Weekday") labels the days of the week.
Questions
What is the difference between SWITCH and IFS?
SWITCH compares one value against a list of exact matches. IFS evaluates separate conditions, so it can handle ranges like B2>=100.
Is SWITCH case-sensitive?
No. "exp" matches "EXP".
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.