How to Use SWITCH in Excel
Replaces long chains of nested IF with one readable list of value-and-result pairs.
What is SWITCH in Microsoft Excel?
SWITCH evaluates an expression once and compares it against a list of values, returning the matching result. It is cleaner than IF(A2="A",...,IF(A2="B",...)) because the expression appears only once and each case sits on its own line. A final argument acts as the default when nothing matches.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| expression | Required | The value or formula to test, evaluated only once. |
| value1 | Required | The first value to compare against the expression. |
| result1 | Required | What to return when expression equals value1. |
| default | Optional | An optional final value returned when nothing matches. |
When to Use SWITCH (Real-World Business Scenarios)
- 1
Mapping short codes to full labels, such as department codes to department names.
- 2
Converting a numeric grade to a letter grade with five or six bands.
- 3
Cleaning imported status values like 'D', 'P', 'C' into Delivered, Pending and Cancelled.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Code | Department |
|---|---|
| FIN | Finance |
| MKT | Marketing |
| ENG | Engineering |
How it works: Compares A2 against each listed code and returns the matching label. Any unknown code falls through to 'Other'.
Common Errors & How to Fix Them
Why it happens: No value matched and no default argument was supplied.
Fix: Add a final default value: =SWITCH(A2,"FIN","Finance","Other").
Why it happens: The pairs were supplied in the wrong order, so a value and its result are swapped.
Fix: Remember the pattern is value, result, value, result. Count the arguments to confirm each pair is complete.
Try SWITCH Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SWITCH)
What does the SWITCH formula do in Excel?
SWITCH evaluates an expression once and compares it against a list of values, returning the matching result. It is cleaner than IF(A2="A",...,IF(A2="B",...)) because the expression appears only once and each case sits on its own line. A final argument acts as the default when nothing matches.
What is the syntax for SWITCH?
What are common mistakes when using SWITCH?
#N/A: No value matched and no default argument was supplied. (Add a final default value: =SWITCH(A2,"FIN","Finance","Other").) Wrong branch taken: The pairs were supplied in the wrong order, so a value and its result are swapped. (Remember the pattern is value, result, value, result. Count the arguments to confirm each pair is complete.)
Related Excel Formulas
Keep building your formula toolkit with these logical functions.
- IFSEvaluates multiple logical conditions sequentially without complex nested IF statements.
- IFERRORCatches and replaces formula errors (#N/A, #VALUE!, #DIV/0!) with clean fallbacks or blanks.
- IFThe foundation of every decision formula in Excel: returns one value when a condition is TRUE and another when it is FALSE.
- IFNACatches only #N/A errors, leaving every other error visible so real bugs are never hidden.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
Master SWITCH in Intermediate Excel
Learn SWITCH along with our formula library, practice files, and verifiable certificates in our complete curriculum.