# SWITCH function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/switch · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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.
- **Syntax:** `=SWITCH(expression, value1, result1, ..., [default])`
- **Most common use:** `=SWITCH(B2,"EXP","Express","STD","Standard","Unknown")` returns Express (A code turned into a label)
- **Available in:** Excel 2019 and later, including Microsoft 365
- **Category:** [Logical functions](https://spreadsheetpractice.com/functions/category/logic)
- **Returns:** The result for the first matching value, the default, or #N/A.
- **Google Sheets:** Available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Order | Ship code |  | Result |
| 2 | O-310 | EXP |  | Express |
| 3 | O-311 | STD |  | 5 |
| 4 | O-312 | PU |  | #N/A |

## 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.

## Related functions

- [IFS](https://spreadsheetpractice.com/functions/ifs): IFS checks a list of conditions in order and returns the value paired with the first one that is TRUE.
- [IF](https://spreadsheetpractice.com/functions/if): IF checks a condition and returns one value when it is true and another when it is false.
- [CHOOSE](https://spreadsheetpractice.com/functions/choose): CHOOSE returns the item at a given position from a list you type into the formula: 1 returns the first item, 2 the second, and so on.
- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.

## 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".

## Practice

A graded SWITCH exercise ("Response times by priority") is on [the SWITCH page](https://spreadsheetpractice.com/functions/switch). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
