Excel SWITCH Function
last modified October 5, 2026
The SWITCH function is a logical function in Excel that compares one
value (called the expression) against a list of values and returns the result
corresponding to the first matching value. If no value matches, it returns an
optional default value, or the #N/A error when no default is
supplied. This tutorial provides a comprehensive guide to using the
SWITCH function with detailed examples. You'll learn basic syntax,
default values, exact matching rules, range comparisons with the
TRUE trick, and how SWITCH compares with nested
IF, IFS, and CHOOSE.
SWITCH Function Basics
The SWITCH function evaluates the expression once and then scans
the value/result pairs in order, returning the result that follows the first
matching value. It's an alternative to nested IF statements, making formulas
cleaner and easier to read.
| Component | Description |
|---|---|
| Function Name | SWITCH |
| Syntax | =SWITCH(expression, value1, result1, [value2, result2], ..., [default]) |
| Arguments | One expression, up to 126 value/result pairs, optional default |
| Return Value | Result for first matching value, the default, or #N/A |
| Availability | Excel 2019, 2021, 2024, Microsoft 365, and Excel for the web |
This table breaks down the essential components of the SWITCH
function. It shows the function name, basic syntax format, argument structure,
return value behavior, and the Excel versions that support it.
| Argument | Meaning |
|---|---|
| expression | Required. The value to compare. It can be a constant, a cell reference, or a formula, and it is evaluated only once. |
| value1 | Required. The first value compared against the expression. |
| result1 | Required. The value returned when value1 matches the
expression. It can be a number, text, or another formula. |
| value2, result2, ... | Optional. Additional pairs, up to 126 pairs in total. They are compared in the order you write them, and the first match decides the result. |
| default | Optional. The final argument when it has no matching value. It is returned when no value matches the expression. |
Every function in Excel is limited to 254 arguments, so SWITCH
accepts up to 126 value/result pairs in addition to the expression and an
optional default. The default is recognized by position: when the number of
arguments after the expression is odd, the last argument is treated as the
default. In Excel 2016 and earlier the function does not exist and produces a
#NAME? error.
Basic SWITCH Example
This example demonstrates the simplest use of the SWITCH function with hard-coded day numbers.
=SWITCH(3, 1, "Monday", 2, "Tuesday", 3, "Wednesday", "Invalid day")
This formula compares the value 3 with the day numbers 1, 2, and 3. It matches the third pair (3, "Wednesday") and returns "Wednesday". The last argument, "Invalid day", is the default value and would be returned for any other number.
SWITCH with Cell References
A more practical use of SWITCH involves referencing cell values.
Here's an example with product codes.
| A | B |
|---|---|
| PRD-002 | |
| =SWITCH(A1, "PRD-001", "Laptop", "PRD-002", "Monitor", "PRD-003", "Keyboard", "Unknown product") |
The table shows a product code in cell A1 and a SWITCH formula in
cell B1 that returns the corresponding product name. The formula matches
"PRD-002" to "Monitor".
=SWITCH(A1, "PRD-001", "Laptop", "PRD-002", "Monitor", "PRD-003", "Keyboard", "Unknown product")
This formula checks the value in A1 against product codes. When A1 contains "PRD-002", it returns "Monitor". If no codes match, it returns "Unknown product". Using cell references makes SWITCH more dynamic.
Default Values and #N/A
When no value matches, the result depends on whether a default has been
provided. Without a default, SWITCH returns the #N/A
error; with a default, it returns that value instead.
=SWITCH(A1, 1, "Poor", 2, "OK", 3, "Good") ' 4 returns #N/A =SWITCH(A1, 1, "Poor", 2, "OK", 3, "Good", "?") ' 4 returns "?"
The first formula returns #N/A for a rating of 4 because no value
matches and no default is supplied. The second adds "?" as the final argument;
because it is the only argument without a matching value, it becomes the
default. To return an empty result instead of an error, use an empty string ""
as the default.
Count the arguments carefully. The formula
=SWITCH(A1, 1, "Poor", 2, "OK", 3, "Good", 4, "?") has an even
number of arguments after the expression, so Excel reads 4 and "?" as a fourth
value/result pair. A rating of 4 now returns "?", but a rating of 5 still
returns #N/A because there is no default. If a value appears in
more than one pair, the first pair always wins.
Exact Matching and Case Sensitivity
SWITCH performs exact matching only: no wildcards and no comparison
operators. Text comparisons are not case-sensitive, so the size codes "m" and
"M" are treated as the same value.
=SWITCH(A1, "S", "Small", "M", "Medium", "L", "Large", "Unknown")
With A1 containing the lowercase "m", this formula returns "Medium", because
matching ignores the case of letters. When case matters, use IFS
with the EXACT function instead, for example
=IFS(EXACT(A1,"M"), "Capital", TRUE, "Other").
Another exact-matching rule concerns data types: text and numbers never match
each other. A code stored as text, such as "1", does not match the number 1,
even though the two look alike. Convert both sides with VALUE or
TEXT when the types may differ.
SWITCH with Numeric Ranges
Because SWITCH matches exact values only, it cannot compare a
score against thresholds directly. The workaround is the TRUE
trick: use TRUE as the expression and write each value as a logical
test. This example shows a grading system.
| A | B |
|---|---|
| 87 | |
| =SWITCH(TRUE, A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", "F") |
This table demonstrates using SWITCH with logical expressions to
create range-based grading. The formula evaluates the score in A1 against grade
thresholds.
=SWITCH(TRUE, A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", "F")
Each logical test returns TRUE or FALSE, and SWITCH compares these
results against the expression, TRUE. For the score 87, the first test is FALSE
and the second is TRUE, so the result is "B". The final argument "F" is the
default, returned when no test is TRUE. The order matters: with overlapping
thresholds the tests must run from the highest to the lowest, or the wrong grade
is returned. This technique extends SWITCH beyond exact matches,
though the IFS function handles logical tests more naturally.
SWITCH with Multiple Conditions
You can use SWITCH to handle multiple conditions by combining
cells into one expression. This example shows department and role combinations.
| A | B | C |
|---|---|---|
| Marketing | Manager | |
| =SWITCH(A1&B1, "MarketingManager", 75000, "MarketingAssociate", 50000, "ITManager", 85000, 40000) |
The table shows how to concatenate values from multiple cells to create
composite keys for SWITCH. The formula combines department and
role to determine salary.
=SWITCH(A1&B1, "MarketingManager", 75000, "MarketingAssociate", 50000, "ITManager", 85000, 40000)
This formula concatenates department (A1) and role (B1) to create a composite
key. For "Marketing" and "Manager", the key is "MarketingManager" and the
result is 75000. Any other combination falls to the default salary of 40000.
Because text matching is case-insensitive, "marketing" and "manager" match as
well. For conditions that combine several tests with AND or
OR, the IFS function is a better fit.
SWITCH with Error Handling
An error in the expression itself propagates to the result: if A1 contains
#N/A, the formula =SWITCH(A1, 1, "Number", "Other")
also returns #N/A, because an error can never match a value. To
classify values that may be errors, convert the error into a matchable code
with the TYPE function, which returns 16 for error values instead
of passing them along. This example shows how.
| A | B |
|---|---|
| #N/A | |
| =SWITCH(TYPE(A1), 1, "Number", 2, "Text", 4, "Logical", 16, "Error", "Unknown type") |
This table demonstrates using SWITCH with the TYPE function to
identify different value types, including errors. The formula handles the #N/A
error in A1.
=SWITCH(TYPE(A1), 1, "Number", 2, "Text", 4, "Logical", 16, "Error", "Unknown type")
With A1 holding #N/A, TYPE returns the code 16, the
pair 16/"Error" matches, and the formula returns "Error". No
IFERROR wrapper is needed because TYPE never fails on
error values. For a number it returns 1, for text 2, for a logical value 4, and
for anything else the default "Unknown type" is used. This technique lets a
single formula manage different data scenarios.
SWITCH with Arrays
In Excel 2021 and Microsoft 365, SWITCH works with arrays. If the
expression is a range, each value in the range is matched separately and the
results spill onto the worksheet, so one formula can translate an entire column
of codes.
=SWITCH(C5:C16, 1, "Poor", 2, "OK", 3, "Good", "?")
This formula is entered once and fills the cells below it with translated codes, one result per row. There is no need to copy the formula down, and the spill range adjusts automatically when the input range changes. In Excel 2019, which lacks dynamic arrays, the formula must be entered in each row separately.
SWITCH Does Not Short-Circuit
You might expect SWITCH to stop evaluating once it finds a match.
It does not. Excel evaluates every value and every result in the formula,
including the pairs that are never used. The returned result is still the first
match, but expensive or volatile calculations run whether or not their branch
is chosen.
For most formulas this makes no practical difference, but it matters when
results involve slow calculations, and it can cause unwanted recursion and a
#NUM! error in recursive LAMBDA functions. When
skipping that work matters, use nested IF or the
CHOOSE function, both of which perform true short-circuit
evaluation and calculate only the branch they return.
SWITCH vs Nested IF vs IFS vs CHOOSE
SWITCH provides a cleaner alternative to nested IF statements. The
two formulas below return the same result for any value of A1.
=IF(A1=1, "Red", IF(A1=2, "Green", IF(A1=3, "Blue", "Unknown")))
=SWITCH(A1, 1, "Red", 2, "Green", 3, "Blue", "Unknown")
The nested version repeats the cell reference in every test and needs a closing
parenthesis for each IF. The SWITCH version names the expression
once and reads as a simple list of values and results, which makes it easier to
write, read, and modify.
IFS is the other close relative. It requires a separate logical
test for each condition, so it handles comparisons and different expressions,
and it needs a TRUE test as its final argument to act as a default.
=SWITCH(A1, 100, "OK", 200, "Warning", 300, "Error", "Invalid") =IFS(A1=100, "OK", A1=200, "Warning", A1=300, "Error", TRUE, "Invalid")
Both return "Invalid" for a code that is not 100, 200, or 300. Use
SWITCH when one value is matched against a list of specific
values, and IFS when the conditions involve comparisons such as
> or combine several expressions. Neither function
short-circuits.
CHOOSE selects by position rather than by value: an index of 1
returns the first result, 2 the second, and so on.
=CHOOSE(A1, "Red", "Green", "Blue") ' by position =SWITCH(A1, 1, "Red", 2, "Green", 3, "Blue") ' by value
CHOOSE is the compact choice when the input is already a small
sequential number, such as the result of WEEKDAY or
MONTH, and it evaluates only the selected result.
SWITCH is better when the values are text, are not sequential, or
when unmatched values need a default, since CHOOSE returns
#VALUE! for an index outside its list.
SWITCH Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Function used in Excel 2016 or earlier | #NAME? error | SWITCH requires Excel 2019 or later; use nested IF or a lookup function |
| No match and no default supplied | #N/A error | Add a default as the single final argument with no matching value |
| Text compared with a number, such as "1" against 1 | The pair never matches | Convert with VALUE or TEXT so both sides share one type |
| Comparison operators such as >= used as values | The test never matches | Use the TRUE trick or the IFS function |
| The intended default is read as another value/result pair | Unmatched values return #N/A | Count the arguments; the default must stand alone as the last argument |
| The expression itself evaluates to an error | The same error (#N/A, #DIV/0!, and so on) | Convert with TYPE or test with ISERROR before matching |
| The expression cell is empty | Treated as 0 during matching | Guard with ISBLANK or supply a default |
| The same value appears in two pairs | Only the first pair is ever returned | Remove the duplicate; the first match wins |
| Slow calculations placed in unused branches | Sluggish recalculation | SWITCH evaluates every result; switch to IF or CHOOSE, which short-circuit |
Most SWITCH problems come from two sources: forgetting the
default, and expecting comparisons that exact matching cannot perform. End the
formula with an unpaired default unless you specifically want
#N/A to signal "no match", and move to IFS or a
lookup table as soon as the conditions involve ranges or need to change often.
The SWITCH function is a versatile tool for simplifying
multi-branch logic in Excel. From translating codes into labels to running a
different calculation for each case, it replaces nested IF chains with a flat,
readable list of value/result pairs. Remember that SWITCH returns
the first match, matches exactly and case-insensitively, treats the final
unpaired argument as the default, and returns #N/A when no default
is given. It accepts up to 126 value/result pairs, requires Excel 2019 or
later, and evaluates every argument rather than short-circuiting. Mastering
SWITCH will make your conditional formulas shorter, clearer, and
easier to maintain.
Author
List all Excel Formulas.