One editable table, two formulas side by side. IF tests any TRUE/FALSE condition and nests an else into every branch; SWITCH matches one expression against discrete values and returns the paired result — cleaner for a code→label dispatch, but it cannot test a range like >=90 directly and returns #N/A when nothing matches. Every result below is evaluated live in HyperFormula 3.5.
SWITCH flattens an equality dispatch (a score or code mapped to a label) into clean value→result pairs and was marginally faster on a 10,000-row ladder in our engine. But SWITCH matches equality only: for a range you must use the SWITCH(TRUE,…) idiom, where IF is clearer. IF keeps a built-in else — with no default IF returns FALSE while SWITCH returns #N/A — and works in every Excel version, where SWITCH needs 2019 / 365.
Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.
| Capability | IF | SWITCH | Why it matters |
|---|---|---|---|
| Matching model | ✓ Any TRUE/FALSE condition | ✓ Equality vs discrete values | IF tests conditions; SWITCH matches an expression to values. |
| Value mapping (code→label) | ◐ Nested IF equality chain | ✓ Flat value→result pairs | SWITCH is cleaner for a dispatch table. |
| Range / threshold tests | ✓ Natural: B2>=90 | ◐ Needs SWITCH(TRUE,…) | SWITCH cannot match a range as a value (measured). |
| Built-in else / default | ✓ Yes — value_if_false | ◐ Optional default argument | Both can fall back; SWITCH's default is a trailing argument. |
| No match, no default | ✓ Returns FALSE | ✗ Returns #N/A | Measured divergence: IF(B2=99,"X")=FALSE, SWITCH(B2,99,"X")=#N/A. |
| Short-circuit evaluation | ✓ Untaken branch never runs | ✓ Later pairs never run | Both stop at the first match. |
| Compatibility | ✓ Excel 2007+ / everywhere | ◐ Excel 2019+ / 365 | IF is the portable choice. |
| Speed (HyperFormula) | ✓ ~5–12ms / 200 grades | ✓ ~5–9ms / 200 grades | Both negligible; SWITCH marginally faster on the ladder. |
IF takes a condition and two branches; SWITCH takes an expression, value→result pairs, and an optional trailing default.
Both formulas read the SAME editable table (Score in B, Dept code in C). Change a value or type your own formula and watch the two results update live.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=IF(B2=92,"Top",IF(B2=78,"Mid","Other"))=SWITCH(B2,92,"Top",78,"Mid","Other")=IF(B2=99,"X",IF(B2=100,"Y","fallback"))=SWITCH(B2,99,"X",100,"Y","fallback")=IF(B2=99,"X")=SWITCH(B2,99,"X")=IF(B2>=90,"A","B")=SWITCH(TRUE,B2>=90,"A","B")=IF(B2>=90,"A","B")=SWITCH(B2,">=90","A","B")=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))=SWITCH(TRUE,B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D","F")=IF(C2="A","Alpha",IF(C2="B","Beta","Unknown"))=SWITCH(C2,"A","Alpha","B","Beta","Unknown")200 graded cells per engine — a 4-level nested IF vs a SWITCH(TRUE,…) ladder — on tables of 1,000 / 5,000 / 10,000 rows. Both ~5–12ms in HyperFormula 3.5; each formula grades one cell so the cost is roughly constant regardless of table size, with SWITCH marginally faster. HyperFormula is NOT Excel — relative signal only.
| Table size | IF | SWITCH |
|---|---|---|
| 1,000 rows × 200 lookups | 12.4 ms | 9.38 ms |
| 5,000 rows × 200 lookups | 11.15 ms | 6.11 ms |
| 10,000 rows × 200 lookups | 5.13 ms | 5.24 ms |
Match the shape of your logic to the function — a value dispatch favors SWITCH, conditions and ranges favor IF.
| Situation | Use | Why |
|---|---|---|
| Map a code or score to a label by exact value | SWITCH | SWITCH lists each value→result once — no repeated expression or nested parentheses. |
| A range or threshold test (>=90, between 80 and 90) | IF | IF tests the condition directly; SWITCH needs the SWITCH(TRUE,…) idiom and cannot match a range as a value. |
| A simple two-way pass/fail branch | IF | IF(cond,then,else) is the shortest form for two outcomes. |
| You need a default when nothing matches | Either | IF has a built-in else; SWITCH takes a trailing default argument. Without one, IF returns FALSE and SWITCH returns #N/A. |
| A decision tree that nests outcomes | IF | IF nests cleanly inside IF; SWITCH results are values, not nested condition chains. |
| AND/OR gates inside the condition | IF | IF accepts AND()/OR() as a test; SWITCH matches equality, so a gate needs the TRUE idiom. |
| The workbook must open in pre-2019 Excel | IF | SWITCH arrived in Excel 2019 / 365; IF works everywhere. |
| You want an explicit error on an unmatched input | SWITCH | SWITCH's #N/A makes a missing mapping visible; IF's else can silently mask it. |
An equality chain becomes flat value→result pairs; a condition ladder needs the SWITCH(TRUE,…) idiom; the else becomes a trailing default.
| Task | IF / old way | SWITCH | What changed |
|---|---|---|---|
| Value dispatch | =IF(B2=92,"Top",IF(B2=78,"Mid","Other")) | =SWITCH(B2,92,"Top",78,"Mid","Other") | Each equality branch becomes one value→result pair; the else becomes the trailing default. |
| Text code mapping | =IF(C2="A","Alpha",IF(C2="B","Beta","Unknown")) | =SWITCH(C2,"A","Alpha","B","Beta","Unknown") | SWITCH shines here — the expression C2 is written once, not repeated per branch. |
| Range test | =IF(B2>=90,"A","B") | =SWITCH(TRUE,B2>=90,"A","B") | SWITCH cannot match >=90 as a value; match TRUE against the condition instead. |
| Grade ladder | =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D"))) | =SWITCH(TRUE,B2>=90,"A",B2>=80,"B",B2>=70,"C","D") | Order thresholds highest-first; both stop at the first match. |
| No-match handling | =IF(B2=99,"X","none") | =SWITCH(B2,99,"X","none") | Give SWITCH an explicit trailing default; omit it and SWITCH returns #N/A while IF returns FALSE. |