Logical

SWITCH Function in Excel

Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

Syntax

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

Arguments

  • expression (required): The value to match against
  • value1 (required): First value to match
  • result1 (required): Result if expression matches value1
  • default (optional): Value to return if no match found

Examples

  • =SWITCH(A1, 1, "January", 2, "February", 3, "March", "Unknown") - Convert number to month - Result: Month name
  • =SWITCH(B1, "S", "Small", "M", "Medium", "L", "Large") - Expand size codes - Result: Full size name

Excel SWITCH function documentation

  • SWITCH is a Logical function in Excel 2016+ that compares one expression against a list of values and returns the first match.
  • Syntax: =SWITCH(expression, value1, result1, [value2, result2], …, [default]).
  • Ideal for region codes, department IDs, and category labels — not for numeric ranges.
  • Related: [IFS](/functions/ifs/) for threshold logic, [IF](/functions/if/) for single conditions.

SWITCH syntax and default argument

  • expression (required): the value to compare — often a cell reference (A2).
  • value/result pairs: exact matches only — SWITCH does not support > or < tests.
  • default (optional final argument): returned when no value matches; omit to get #N/A.
  • Example: =SWITCH(A2, "East", 0.05, "West", 0.07, "Central", 0.06, 0).

Step-by-step: replace nested IF with SWITCH

  • Step 1 — Confirm each outcome maps to an exact value (not a range).
  • Step 2 — List pairs: "East" → 5%, "West" → 7%, etc.
  • Step 3 — =SWITCH(B2, "Open", "Active", "Closed", "Done", "Pending") for status codes.
  • Step 4 — Add numeric default 0 or text "Unknown" as the last argument.
  • Step 5 — For range logic (score > 90), use [IFS](/functions/ifs/) instead.

SWITCH vs IFS vs nested IF

  • SWITCH — one expression vs exact values; compact for code lookups.
  • IFS — multiple independent logical tests; use for numeric tiers.
  • Nested IF — still valid for two outcomes; SWITCH/IFS improve readability at scale.
  • Compare: [IF vs IFS](/compare/if-vs-ifs/) · [IF function](/functions/if/).

Worked examples to copy

  • Region multiplier: =SWITCH(C2,"N",1.1,"S",0.95,"E",1,1).
  • Day name to number: =SWITCH(A2,"Mon",1,"Tue",2,"Wed",3,0).
  • Error label: =SWITCH(LEFT(A2,3),"#N/","Lookup","#VA","Value","OK").
  • With expression: =SWITCH(MONTH(A2),1,"Q1",2,"Q1",3,"Q1",4,"Q2") — consider IFS for ranges.

People also ask

  • SWITCH vs IFS? — SWITCH matches exact values; IFS handles inequalities and AND logic.
  • SWITCH #N/A? — No match and no default argument provided.
  • Can SWITCH use wildcards? — No. Use IF with SEARCH or XMATCH match_mode 2.
  • SWITCH case sensitivity? — Text comparisons are case-insensitive in Excel by default.

Common errors

  • #N/A when no match and no default provided
  • #VALUE! with mismatched pairs

Use cases

  • Code translations
  • Category mapping
  • Status conversions

Frequently asked questions

  • What is SWITCH and when should I use it? SWITCH matches an expression against specific values and returns corresponding results. Use it when you're comparing one value against multiple exact matches. It's cleaner than nested IFs for value-based selection: =SWITCH(A1, 1, "One", 2, "Two", "Other").
  • What is the difference between SWITCH and IFS? SWITCH compares one expression against multiple values (equality check). IFS evaluates multiple independent conditions (can use >, <, etc.). Use SWITCH for exact matches; use IFS for range-based or complex conditions.
  • How do I add a default value in SWITCH? Add an odd argument at the end: =SWITCH(A1, 1, "One", 2, "Two", "Default"). If the number of arguments after expression is odd, the last one is the default. Without a default, SWITCH returns #N/A for no match.
  • Is SWITCH available in older Excel versions? SWITCH is available in Excel 2019 and Excel 365. For older versions, use nested IF statements or CHOOSE (if values are sequential numbers starting from 1). VLOOKUP with a lookup table is another alternative.
  • Can SWITCH handle multiple matches for the same result? No, each value needs its own result in SWITCH. For multiple values returning the same result, use IFS with OR: =IFS(OR(A1=1,A1=2),"Group1", OR(A1=3,A1=4),"Group2"). Or use VLOOKUP with a mapping table.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SWITCH

  • Code translations — common Logical scenario for SWITCH.
  • Category mapping — common Logical scenario for SWITCH.
  • Status conversions — common Logical scenario for SWITCH.

SWITCH in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • SWITCH syntax: =SWITCH(expression, value1, result1, [value2, result2], ..., [default])
  • expression (required): The value to match against
  • value1 (required): First value to match
  • result1 (required): Result if expression matches value1
  • default (optional): Value to return if no match found
  • Confirm SWITCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SWITCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IFS (/functions/ifs/): Checks multiple conditions and returns a value corresponding to the first TRUE condition.
  • CHOOSE (/functions/choose/): Returns a value from a list of values based on an index number.
  • IF (/functions/if/): Returns one value if a condition is TRUE and another value if it's FALSE.
  • VLOOKUP (/functions/vlookup/): Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.

Comparisons involving this function

  • IF vs IFS (/compare/if-vs-ifs/): Use IF for one condition with two outcomes. Use IFS when you need three or more ordered conditions without building a hard-to-read nested IF chain.

Errors to watch for

  • #N/A when no match and no default provided — review causes on linked error pages in the directory.
  • #VALUE! with mismatched pairs — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SWITCH on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SWITCH worked examples to copy

  • =SWITCH(A1, 1, "January", 2, "February", 3, "March", "Unknown") — Convert number to month. Expected result: Month name.
  • =SWITCH(B1, "S", "Small", "M", "Medium", "L", "Large") — Expand size codes. Expected result: Full size name.

SWITCH reference summary for crawlers and offline review

  • SWITCH belongs to the Logical category in Excel. Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
  • Full syntax: =SWITCH(expression, value1, result1, [value2, result2], ..., [default]). Open /functions/switch/ for parameters, FAQs, and related pages.
  • Common mistakes: #N/A when no match and no default provided; #VALUE! with mismatched pairs
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.