Excel SWITCH Function

SWITCH compares one value against a list of possible values and returns the result tied to the first match it finds. If nothing matches, it can return a default value instead of an error. The catch: SWITCH only checks for exact matches, so you can't use it directly for "greater than" or "less than" logic. There's a workaround for that below.

Available in: Excel 365, Excel 2021, and Excel 2019. Not available in Excel 2016 or earlier.

Syntax

=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Parameter	Required	Description
expression	Yes	The value being tested. Usually a cell reference, but it can be any formula that returns a single value.
value1	Yes	The first value to compare expression against.
result1	Yes	What SWITCH returns if expression matches value1.
value2, result2, ...	No	Additional value/result pairs. You can add up to 126 pairs total.
default	No	Returned when expression matches none of the listed values. If you leave this out and nothing matches, SWITCH returns #N/A.

Basic Example

You're building a customer satisfaction tracker where reps enter a numeric score from 1 to 5, and you want a readable label next to each score.

=SWITCH(C2, 1, "Poor", 2, "Fair", 3, "Good", 4, "Great", 5, "Excellent", "Not rated")

// C2 = the score being tested
// 1, 2, 3, 4, 5 = the possible scores
// "Poor" through "Excellent" = the label returned for each match
// "Not rated" = the default, used when C2 isn't 1 through 5

SWITCH checks C2 against each number in order. A score of 4 in C2 returns "Great." A blank cell, a typo, or a score of 6 falls through to "Not rated" instead of throwing an error.

How SWITCH Works

It only matches exactly

SWITCH tests for equality, not ranges. It can tell you whether C2 equals 3, but it has no built-in way to ask whether C2 is greater than 3. For that kind of logic, you need a workaround (covered in the gotchas below) or a different function entirely.

It checks every pair, even after finding a match

This one surprises people. SWITCH doesn't stop calculating once it finds a hit. Excel evaluates every value and result argument in the formula regardless of which one ends up matching. With simple text and numbers this makes no practical difference, but if any of your result arguments contain heavy calculations, all of them run every time.

Text comparisons aren't case-sensitive

=SWITCH(A2, "gold", "Top Tier", "silver", "Mid Tier", "Standard") matches "Gold," "GOLD," and "gold" identically. If you need case-sensitive matching, SWITCH isn't the right tool; you'd need EXACT nested inside an IF.

A default is optional but rarely skippable in practice

Leave off the default and any unmatched value returns #N/A. In a live dashboard, that error can propagate into every formula downstream that references the SWITCH result. Add a default even when you think every case is covered. Data always finds a way to break that assumption.

Common Use Cases

Turning a weekday number into a day name

You want a readable day name next to a date column, driven off WEEKDAY instead of typing out seven IF conditions.

=SWITCH(WEEKDAY(A2), 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday")

WEEKDAY converts the date in A2 to a number from 1 to 7, and SWITCH translates that number into a label. No nested IFs required.

Expanding department codes into full names

Your HR export uses three-letter codes, and you need the full department name for a report.

=SWITCH(B2, "HR", "Human Resources", "FIN", "Finance", "OPS", "Operations", "MKT", "Marketing", "Unknown Department")

// B2         = the department code
// "HR"..."MKT" = the codes you're expecting
// "Unknown Department" = the default for anything else, like a typo or a new code nobody added yet

Mapping a size code to a display label

An order sheet stores product sizes as single letters, and the shipping label needs the full word.

=SWITCH(D2, "S", "Small", "M", "Medium", "L", "Large", "XL", "Extra Large", "Check Size")

If someone enters "sm" or "Lg" instead of the expected codes, the formula falls to "Check Size" rather than returning a wrong or blank result.

Handling Errors

SWITCH throws #N/A when the expression doesn't match any listed value and no default is provided. In older Excel builds that don't support SWITCH, the formula returns #NAME? instead, because Excel doesn't recognize the function name at all.

Common causes of #N/A:

• The default argument was left off
• A trailing space or hidden character in the source data ("HR " instead of "HR")
• A number stored as text being compared against a numeric value, or the reverse
• A new code or category appeared in the data that isn't listed in the formula

=IFNA(SWITCH(B2, "HR", "Human Resources", "FIN", "Finance"), "Code not recognized")

Notes & Gotchas

What is the SWITCH function used for in Excel?

SWITCH is used to translate one value into another based on a list of exact matches, like turning a numeric code into a text label. It's most useful when you have several possible values to check against a single expression, replacing what would otherwise be a long chain of nested IF statements.

What is the difference between SWITCH and IFS functions in Excel?

SWITCH tests one expression against a list of exact values. IFS evaluates a separate logical test for each condition, so it can handle "greater than," "less than," and other comparisons directly. SWITCH also supports a built-in default result if nothing matches; IFS returns #N/A unless you add a final TRUE condition yourself.

Which versions of Excel support the SWITCH function?

SWITCH works in Excel 365, Excel 2021, and Excel 2019. It is not available in Excel 2016 or any earlier version, including boxed or perpetual-license copies of Excel 2016, regardless of whether an Office 365 subscription is active elsewhere on the machine. If a workbook using SWITCH is opened in an unsupported version, the formula shows #NAME?.

How do you use SWITCH instead of nested IF statements?

Replace each IF condition with a value/result pair inside SWITCH, using the same expression for every comparison. A five-level nested IF checking C2 against five ratings becomes one flat SWITCH formula listing five value/result pairs, plus an optional default. The result does the same job with far less punctuation to track.

What is a good alternative to the SWITCH function in older Excel versions?

Nested IF statements work in every Excel version, including 2010 and earlier, and produce the same result as SWITCH for exact-match scenarios. IFS is a middle option: it's shorter to write than nested IFs but, like SWITCH, only reached Excel through the same 2019-and-365-era updates, so it won't help if you're stuck on a much older build.

Why doesn't SWITCH support greater than or less than comparisons?

SWITCH only performs exact matches, so >, <, and similar operators have no direct place inside it. The common workaround is to set expression to TRUE and write each value as a logical test:

=SWITCH(TRUE, A2>=1000, "Gold", A2>=500, "Silver", "Bronze")

This works because SWITCH is really just checking whether each condition evaluates to TRUE. For anything past two or three range breaks, IFS is usually more readable than forcing SWITCH into this pattern.

Does SWITCH stop calculating once it finds a match?

No. Excel evaluates every value and result argument in the formula, even the ones that never get returned. For plain text or number lookups this costs nothing noticeable, but if a result argument runs a heavy calculation, like a nested lookup or a volatile function, that calculation runs on every recalculation regardless of which branch actually matches. Nested IF, by comparison, stops as soon as it hits a TRUE condition, so it can be the faster choice when any branch is expensive to compute.

Related Functions

Function	Use this when...
IFS	You need range-based logic like "greater than" or "less than" instead of exact matches.
IF	You have only one or two conditions and don't need a full SWITCH or IFS setup.
VLOOKUP	Your value/result pairs live in a table on the worksheet instead of hardcoded in the formula.