SWITCH compares one expression against a list of values and returns the result paired with the first match. It replaces long chains of nested IFs with a single, readable formula.
=SWITCH(expression, value1, result1, ..., [default])What does SWITCH do?
SWITCH is a logical function that looks at one value, compares it with a list of possible matches, and returns the result attached to the first match it finds. If nothing matches, it returns an optional default value. The syntax is =SWITCH(expression, value1, result1, value2, result2, ..., [default]).
The function matters because it keeps formulas short and easy to audit. Instead of stacking several IF functions inside one another, you list value-and-result pairs in a straight line. Anyone reading the formula can see exactly which input produces which output.
A typical situation is a sales report where each region must map to a manager, or a grade sheet where a letter grade must become a comment. SWITCH handles those lookups in one cell without a separate reference table. It is available in Excel 2019, Excel 2021, Excel for Microsoft 365, and Excel for the web.
When to use SWITCH
- Converting short codes such as region codes or department codes into full names.
- Turning numeric scores into letter grades or pass/fail labels.
- Assigning a status message based on an order status value.
- Mapping month numbers to month names in a dashboard.
- Choosing a shipping cost band from a size or weight category.
- Replacing nested IF formulas that all test the same cell.
SWITCH syntax
=SWITCH(expression, value1, result1, ..., [default])| Argument | Required? | What to enter |
|---|---|---|
expression | Required | The value or cell reference you want to test. Example: B2 containing "North". |
value1 | Required | The first value to compare with the expression. Example: "North". |
result1 | Required | What to return when the expression equals value1. Example: "Ava Patel". |
value2, result2, ... | Optional | Additional value-and-result pairs, added in pairs. Example: "South", "Liam Chen". |
default | Optional | The value returned when no pair matches. If omitted and nothing matches, SWITCH returns #N/A. Example: "Unassigned". |
What it returns: SWITCH returns the result paired with the first value that matches the expression, or the default value when no match is found. Without a default, an unmatched expression returns #N/A.
SWITCH examples
1 Example 1: Map a region code to a manager
The goal is to show the manager responsible for each region. The formula =SWITCH(A2,"North","Ava Patel","South","Liam Chen","East","Noah Kim","West","Mia Garcia","Unassigned") tests the region in A2. It compares that value with North, South, East, and West in order and returns the matching manager. If the region is not in the list, it returns Unassigned. For A2 containing North, the result is Ava Patel.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Sales | Quarter | |||
| 2 | North | 1200 | Q1 | Ava Patel | ||
| 3 | South | 950 | Q1 | |||
| 4 | East | 1430 | Q2 | |||
| 5 | West | 780 | Q2 | |||
| 6 | North | 1610 | Q3 | |||
| 7 |
2 Example 2: Turn a numeric score into a letter grade
The goal is to convert a score into a grade band. The formula =SWITCH(B2,95,"A",85,"B",75,"C",65,"D","F") checks the score in B2 against exact values. SWITCH matches exact values only, so this works when scores are rounded to the listed bands. A score of 95 returns A, 85 returns B, and any other score returns F. For B2 equal to 85, the result is B.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Class | |||
| 2 | Jordan | 85 | Maths | B | ||
| 3 | Priya | 95 | Maths | |||
| 4 | Sam | 75 | Science | |||
| 5 | Elena | 65 | Science | |||
| 6 | Marcus | 55 | History | |||
| 7 |
3 Example 3: Convert an order status into a customer message
The goal is to give customers a clear message for each order status. The formula =SWITCH(C2,"New","Order received","Packed","Being packed","Shipped","On the way","Delivered","Delivered","Check status") tests the status in C2. It returns the matching message for New, Packed, Shipped, or Delivered. Any other status returns Check status. For C2 containing Shipped, the result is On the way.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Customer | Status | |||
| 2 | 1001 | Riley | Shipped | On the way | ||
| 3 | 1002 | Morgan | New | |||
| 4 | 1003 | Casey | Packed | |||
| 5 | 1004 | Taylor | Delivered | |||
| 6 | 1005 | Jamie | Returned | |||
| 7 |
4 Example 4: Map a department code to a cost centre
The goal is to assign the correct cost centre to each expense. The formula =SWITCH(B2,"FIN","CC-100","HR","CC-200","IT","CC-300","OPS","CC-400","CC-999") tests the department code in B2. It compares the code with FIN, HR, IT, and OPS and returns the matching cost centre. Unknown codes return CC-999. For B2 containing HR, the result is CC-200.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Amount | |||
| 2 | Alex | HR | 450 | CC-200 | ||
| 3 | Blake | FIN | 1200 | |||
| 4 | Drew | IT | 800 | |||
| 5 | Emery | OPS | 300 | |||
| 6 | Finley | MKT | 650 | |||
| 7 |
5 Example 5: Choose a shipping band from a size category
The goal is to set a shipping charge based on the package size. The formula =SWITCH(C2,"S",5,"M",8,"L",12,"XL",18,0) tests the size in C2. It returns 5 for S, 8 for M, 12 for L, and 18 for XL. Any other size returns 0. For C2 containing L, the result is 12.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Size | |||
| 2 | Notebook | 3 | L | 12 | ||
| 3 | Pen pack | 10 | S | |||
| 4 | Folder | 5 | M | |||
| 5 | Monitor | 1 | XL | |||
| 6 | Stapler | 2 | M | |||
| 7 |
Common SWITCH errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | No value matched the expression and no default value was supplied. | Add a final default argument, such as "Not found" or 0, after the last result. |
| #VALUE! | The formula has an odd number of arguments after the expression, so a value is missing its result. | Check that every value is followed by exactly one result, and that the default is a single final argument. |
| #NAME? | The function name is misspelled or the workbook uses an older Excel version that does not support SWITCH. | Type =SWITCH( and let Excel suggest the name, or use nested IF functions if your version lacks SWITCH. |
| Wrong result | Text values differ by case or by hidden spaces, so the match fails. | Use TRIM to remove spaces and make sure the compared text uses consistent casing, or use UPPER inside the expression. |
| #VALUE! with ranges | The expression or a value argument refers to a range of cells instead of a single cell. | Point the expression to one cell and list each comparison value separately. |
Tips for using SWITCH
- Always add a default value so unmatched entries return a clear message instead of
#N/A. - Use SWITCH when every test compares the same cell; use IFS when each test has a different condition.
- Keep value-and-result pairs on separate lines in the formula bar to make long lists easier to read.
- Remember that SWITCH matches exact values only; for ranges such as 90 and above, use IFS or a lookup table.
- Wrap the expression in
TRIM()if imported data may contain extra spaces.
Frequently asked questions
What is the difference between SWITCH and IFS?
SWITCH compares one expression against a list of exact values. IFS evaluates separate conditions, so it is better when each test uses a different comparison or a range.
Does SWITCH work in older versions of Excel?
SWITCH is available in Excel 2019, Excel 2021, Excel for Microsoft 365, and Excel for the web. Older versions return #NAME? and need nested IF formulas instead.
Can SWITCH return a blank cell?
Yes. Use "" as a result or as the default value to return an empty text string.
Is SWITCH case-sensitive?
No. SWITCH treats text values as case-insensitive, so North and north match the same value.
What happens if two values match?
SWITCH returns the result of the first matching pair, so place the most specific values earlier in the list.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SWITCH reference