One expression, a list of values, the first match wins. Syntax, a live status-code playground, the exact-equality rule, the #N/A default trap, and how SWITCH differs from IFS — plus the errors people actually hit, sourced and fixed.
=SWITCH(expression, value1, result1, [value2, result2], …, [default])
| Argument | Required | What it does |
|---|---|---|
| expression | yes | The value compared against each listed value — a cell, a number, text, or a formula. |
| value1…value126 | 1+ pairs | Values compared to expression with exact equality. Up to 126 of them. |
| result1…result126 | 1+ pairs | Returned when the paired value is the first exact match. |
| default | no | A final argument with no paired result; returned when nothing matches. Omit it and unmatched codes give #N/A. |
Text matching is case-insensitive; number and text values are distinct types. The first matching value wins, so duplicate values later in the list are never reached.
Behavior measured in a real formula engine — not copied from a docs page. SWITCH compares one expression against each value with exact equality and returns the first match's result. Three rules decide what you get:
| Code | =SWITCH(A2,1,"New",2,"Open",3,"Closed","Unknown") |
|---|---|
| 1 | "New" |
| 2 | "Open" |
| 3 | "Closed" |
| 9 | "Unknown" (default) |
expression = value (exact), not ranges. The first matching value wins; a duplicate value later in the list is never reached.| Formula on code 9 | Result |
|---|---|
=SWITCH(A5,1,"New",2,"Open",3,"Closed") | #N/A |
=SWITCH(A5,1,"New",2,"Open",3,"Closed","Unknown") | "Unknown" |
| SWITCH | IFS | IF | |
|---|---|---|---|
| Max | 126 values | 127 pairs | 64 levels |
| Match | exact = | ranges | one test |
| Default | built-in | add TRUE() | built-in |
| Best for | codes & abbrevs | range bands | 1–2 branches |
=SWITCH(TRUE(),A5>=9,"high",A5>=5,"mid","low") works — Exceljet documents it as an IFS alternative.Method: HyperFormula 3.5 live evaluation of SWITCH dispatch, default, case and type behavior on the playground grid. Reproducible; engine-measured, not transcribed.
Edit the code/status data, then load a preset to see exact dispatch, the #N/A default, case-insensitivity, text≠number, duplicate first-match, or the SWITCH(TRUE()) range trick. Runs on a real engine — no add-in.
10 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
SWITCH is modern (2019+/365). Older Excel returns #NAME?. Here's the exact support across 8 platforms, plus the nested-IF fallback.
| Platform | Support | Notes | Gotcha |
|---|
SWITCH, IFS, IF, IFERROR, AND/OR/NOT — when to dispatch on exact values, when to band ranges, and how to keep formulas readable. Video walkthroughs + downloadable workbooks.