How to Use the SWITCH Function in Excel (With Examples)

The SWITCH function in Excel compares one value against a list of candidate values and returns the result paired with the first match — a cleaner, flatter replacement for long chains of nested IFs. Instead of repeating IF(...) over and over, you write the expression once, list each value and its result, and optionally end with a default. This guide walks through the syntax, a step-by-step first formula, copy-ready examples, and the limits you should know before switching from IF.

How to Use the SWITCH Function in Excel (With Examples)

What Is the SWITCH Function in Excel

The SWITCH function evaluates one expression — a cell reference, a constant, or another formula — and checks it against up to 126 value/result pairs. It returns the result of the first value that matches, or an optional default if nothing matches. Think of it as a self-contained lookup table: no helper range, no separate VLOOKUP table, just one formula.

Typical jobs for the SWITCH function in Excel include converting codes to labels (S → Small), classifying records (weekend vs weekday), and picking a calculation based on a unit. Matching is exact and case-insensitive: text "1" does not match the number 1, and wildcards are not supported — for pattern matching you need Excel wildcards with functions like COUNTIF instead.

SWITCH matches one expression to many results

Use Word, Excel, and PPT for FREE

Free Download

SWITCH Function Syntax and Arguments

The syntax of the SWITCH function is:

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

• expression (required): the value to compare — a cell, a number, text, or a formula such as WEEKDAY(B5).

• value1, result1: the first candidate value and what to return when it matches. You can supply up to 126 pairs.

• default (optional): the last argument, written alone with no matching value. It is returned when nothing matches. Without it, an unmatched expression returns #N/A.

SWITCH is available in Excel 2019 and later, including Microsoft 365, and works the same way in WPS Spreadsheet. Results can be text, numbers, or formulas, so one SWITCH can output labels for some codes and calculations for others.

How to Use the SWITCH Function in WPS

Here is a complete first formula, converting size codes into readable labels:

1. Select the result cell. Click the first empty cell next to your data, for example B2 beside a size code in A2.

2. Type the expression. Start with =SWITCH(A2 — this is the value every candidate will be tested against.

3. Add value/result pairs. Continue with "S","Small","M","Medium","L","Large". Each candidate value is immediately followed by the result to return.

4. Close with a default. End the formula with "Unknown" as the final argument, then press Enter: =SWITCH(A2,"S","Small","M","Medium","L","Large","Unknown").

5. Fill down. Drag the fill handle over the remaining rows. The unmatched code XL correctly falls through to Unknown.

   

SWITCH Function Examples You Can Copy

Three everyday patterns cover most SWITCH needs:

1. Translate ratings: =SWITCH(A2,1,"Poor",2,"OK",3,"Good","?") turns scores into words, with "?" as the catch-all.

2. Classify weekdays: =SWITCH(WEEKDAY(B5),1,"Weekend",7,"Weekend","Weekday") feeds the day number into SWITCH and flags Saturdays and Sundays.

3. Tier sales with comparisons: SWITCH only does exact matching, so for thresholds set the expression to TRUE: =SWITCH(TRUE,C5>=1000,"Gold",C5>=500,"Silver","Bronze"). The first condition that evaluates to TRUE wins.

SWITCH examples for ratings, weekdays and tiers

Use Word, Excel, and PPT for FREE

Free Download

SWITCH Function vs Nested IF and IFS

Nested IF repeats the target cell at every level and piles up closing parentheses — three levels are readable, six are not. IFS improves on that but still restates each full condition. SWITCH reads the expression once, which makes long exact-match lists both shorter and easier to audit.

• Use SWITCH when one value is tested against many exact candidates: codes, statuses, units, menu picks.

• Use IFS when each condition is different (ranges, AND/OR logic) and no single expression repeats.

• Use nested IF only for one or two simple branches, or when you must support Excel 2016 and older, where SWITCH does not exist.

One caution: SWITCH evaluates every result argument rather than stopping at the first match, so avoid parking very heavy calculations inside rarely used pairs.

Nested IF tower versus one SWITCH card

SWITCH Function Limits and Common Errors

Four limits explain almost every "my SWITCH doesn't work" moment:

• #N/A with no default. If nothing matches and you did not supply a final default argument, SWITCH returns #N/A. Add one trailing value with no result to fix it.

• Exact match only. No wildcards, no > or <<> inside the value list. Use the TRUE-expression trick shown above, or IFS.

• Text versus numbers. A text "1" does not match the number 1 — clean the source column or align the types.

• Duplicate values. When the same value appears twice, the first pair wins; keep the list deduplicated to avoid surprises.

The Best Free Alternative: WPS Office for the SWITCH Function

Every formula in this guide runs in WPS Spreadsheet, part of the free WPS Office suite. You get the same SWITCH, IFS, and lookup functions, full .xlsx compatibility, and a familiar ribbon layout — without a Microsoft 365 subscription. WPS AI can even draft or explain a SWITCH formula for you, which is handy when a long value list needs a second pair of eyes.

WPS Spreadsheet

FAQs

1. What does the SWITCH function do in Excel?
It compares one expression against up to 126 candidate values and returns the result paired with the first match, or a default value if nothing matches.

2. Which Excel versions have SWITCH?
Excel 2019 and later, including Microsoft 365. In Excel 2016 and older, use nested IF or lookup functions instead.

3. Why does SWITCH return #N/A?
No candidate value matched and the formula has no default. Add one final argument with no result pair — it becomes the value returned when nothing matches.

4. Can SWITCH handle greater-than comparisons?
Not directly in the value list. Set the expression to TRUE and use conditions like C5>=1000 as the values, or switch to IFS.

5. Is SWITCH the same as IFS?
No. SWITCH tests a single expression against exact values; IFS evaluates a list of independent conditions top to bottom. Use SWITCH for code-to-label maps and IFS for varied conditions.

Free Office Download

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis