Function comparison · engine-measured

IF vs SWITCH

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.

fx
IF(…)  vs  SWITCH(…)
7
measured scenarios
#N/A
SWITCH with no default
FALSE
IF with no else
2019+
SWITCH availability
▶ Try both live See the feature matrix
🏆Verdict: SWITCH for value mapping; IF for conditions, ranges and a default.

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.

IF strengths

  • Tests any condition — ranges, AND/OR, not just equality
  • Built-in else — returns FALSE, never #N/A, when nothing matches
  • Nests into decision trees
  • Works in every Excel version back to 2007

SWITCH strengths

  • Flat value→result pairs read top to bottom
  • No repeated expression or nested parentheses for a dispatch
  • Marginally faster on a long ladder in our engine
  • Optional default argument; explicit #N/A surfaces an unmatched input

Feature-by-feature matrix

Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.

CapabilityIFSWITCHWhy it matters
Matching model✓ Any TRUE/FALSE condition✓ Equality vs discrete valuesIF tests conditions; SWITCH matches an expression to values.
Value mapping (code→label)◐ Nested IF equality chain✓ Flat value→result pairsSWITCH 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 argumentBoth can fall back; SWITCH's default is a trailing argument.
No match, no default✓ Returns FALSE✗ Returns #N/AMeasured divergence: IF(B2=99,"X")=FALSE, SWITCH(B2,99,"X")=#N/A.
Short-circuit evaluation✓ Untaken branch never runs✓ Later pairs never runBoth stop at the first match.
Compatibility✓ Excel 2007+ / everywhere◐ Excel 2019+ / 365IF is the portable choice.
Speed (HyperFormula)✓ ~5–12ms / 200 grades✓ ~5–9ms / 200 gradesBoth negligible; SWITCH marginally faster on the ladder.

Syntax, side by side

IF takes a condition and two branches; SWITCH takes an expression, value→result pairs, and an optional trailing default.

IF

=IF(logical_test, value_if_true, [value_if_false])
logical_testAny expression that evaluates to TRUE or FALSE — comparisons, AND/OR, ISNUMBER…
value_if_trueReturned when the test is TRUE.
value_if_falseThe else branch — returned when FALSE. Omit it and IF returns FALSE. Nest another IF here for more cases.
=IF(B2>=90,"A","B")

SWITCH

=SWITCH(expression, value1, result1, [value2, result2], …, [default])
expressionThe value to match — a cell, a calculation, or TRUE for the condition-ladder idiom.
value1, result1 …Up to 126 value→result pairs. The first value EQUAL to expression wins; matching is equality, not a condition.
defaultA trailing odd argument returned when no value matches. Omit it and SWITCH returns #N/A.
=SWITCH(B2,92,"Top",78,"Mid","Other")

Live head-to-head playground

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.

A1Sample data — click any cell to edit; both formulas re-evaluate live

IF

—

SWITCH

—
HyperFormula 3.5 · live

Every difference, measured

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.

Value mapping — nested IF vs flat SWITCH

Map Alice's score (B2 = 92) to a label by exact value.
IF
=IF(B2=92,"Top",IF(B2=78,"Mid","Other"))
Top
SWITCH
=SWITCH(B2,92,"Top",78,"Mid","Other")
Top
Both return Top for Alice's 92. The logic is identical, but SWITCH writes the expression B2 once and lists flat value→result pairs, while IF repeats B2 in every nested equality test.

No match — IF's else vs SWITCH's default

Alice scores 92; neither 99 nor 100 matches. Both fall through to a default.
IF
=IF(B2=99,"X",IF(B2=100,"Y","fallback"))
fallback
SWITCH
=SWITCH(B2,99,"X",100,"Y","fallback")
fallback
Both return fallback: 92 matches neither 99 nor 100. IF's final else and SWITCH's trailing default argument play the same role — a catch-all for unmatched inputs.

No default — IF returns FALSE, SWITCH returns #N/A

Alice scores 92; the only branch checks 99 and no else/default is given.
IF
=IF(B2=99,"X")
FALSE
SWITCH
=SWITCH(B2,99,"X")
#N/A
The key divergence: with no else/default and no match, IF(B2=99,"X") returns FALSE while SWITCH(B2,99,"X") returns #N/A. Give SWITCH a trailing default to avoid the error; IF silently yields FALSE.

Range test — IF is natural, SWITCH needs TRUE

Is Alice's score >= 90? IF tests the condition directly; SWITCH matches TRUE.
IF
=IF(B2>=90,"A","B")
A
SWITCH
=SWITCH(TRUE,B2>=90,"A","B")
A
Both return A. IF tests B2>=90 directly; SWITCH cannot match a range as a value, so it uses the SWITCH(TRUE,…) idiom — matching TRUE against the condition B2>=90.

The equality trap — SWITCH cannot match a range as a value

Putting ">=90" as a SWITCH value compares the literal text to 92, so it never matches.
IF
=IF(B2>=90,"A","B")
A
SWITCH
=SWITCH(B2,">=90","A","B")
B
The trap, measured: IF returns A but SWITCH returns B (the default). SWITCH(B2,">=90",…) compares the literal text ">=90" to the number 92 — never equal — so it falls through. SWITCH matches equality, not conditions.

Grade ladder — nested IF vs SWITCH(TRUE,…)

Assign A/B/C/D/F to Alice's 92 with four thresholds.
IF
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
A
SWITCH
=SWITCH(TRUE,B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D","F")
A
Both return A for Alice's 92. A four-level nested IF and a SWITCH(TRUE,…) ladder grade identically; order thresholds highest-first because both stop at the first match.

Text mapping — dept code to team name

Map Alice's dept code (C2 = "A") to a team name by exact value.
IF
=IF(C2="A","Alpha",IF(C2="B","Beta","Unknown"))
Alpha
SWITCH
=SWITCH(C2,"A","Alpha","B","Beta","Unknown")
Alpha
Both return Alpha for dept code A. This is SWITCH's home turf — a text dispatch where the expression C2 is written once instead of repeated in each IF branch.

Engine benchmark

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 sizeIFSWITCH
1,000 rows × 200 lookups12.4 ms9.38 ms
5,000 rows × 200 lookups11.15 ms6.11 ms
10,000 rows × 200 lookups5.13 ms5.24 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Both walk the same condition ladder; absolute times are engine-specific and only meaningful as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Match the shape of your logic to the function — a value dispatch favors SWITCH, conditions and ranges favor IF.

SituationUseWhy
Map a code or score to a label by exact valueSWITCHSWITCH lists each value→result once — no repeated expression or nested parentheses.
A range or threshold test (>=90, between 80 and 90)IFIF tests the condition directly; SWITCH needs the SWITCH(TRUE,…) idiom and cannot match a range as a value.
A simple two-way pass/fail branchIFIF(cond,then,else) is the shortest form for two outcomes.
You need a default when nothing matchesEitherIF 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 outcomesIFIF nests cleanly inside IF; SWITCH results are values, not nested condition chains.
AND/OR gates inside the conditionIFIF accepts AND()/OR() as a test; SWITCH matches equality, so a gate needs the TRUE idiom.
The workbook must open in pre-2019 ExcelIFSWITCH arrived in Excel 2019 / 365; IF works everywhere.
You want an explicit error on an unmatched inputSWITCHSWITCH's #N/A makes a missing mapping visible; IF's else can silently mask it.

IF → SWITCH migration

An equality chain becomes flat value→result pairs; a condition ladder needs the SWITCH(TRUE,…) idiom; the else becomes a trailing default.

TaskIF / old waySWITCHWhat 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.

Frequently asked questions

What is the difference between IF and SWITCH?
IF evaluates a TRUE/FALSE condition and returns one of two branches; you nest IFs for more cases. SWITCH matches one expression against a list of discrete values (equality only) and returns the paired result, with an optional trailing default. Use SWITCH for a value dispatch (code→label); use IF for conditions, ranges and AND/OR logic.
When should I use SWITCH instead of nested IF?
Use SWITCH when you map a single value to a result by exact equality — a status code to a name, a score to a label, a category to a rate. It writes the expression once and lists flat value→result pairs, which is cleaner than repeating the expression in every nested IF. Keep IF for range tests, two-way branches, decision trees, and files that must open in Excel 2016 or older.
Can SWITCH do greater-than or range tests?
Not directly. SWITCH matches equality, so =SWITCH(B2,">=90","A","B") compares the literal text ">=90" to the value and never matches (measured: it falls through to the default). For a condition ladder use the idiom =SWITCH(TRUE,B2>=90,"A",B2>=80,"B","C"), which matches TRUE against each condition — or just use IF/IFS, which are clearer for ranges.
Why does SWITCH return #N/A?
Because no value matched the expression and no default argument was given. SWITCH does not fall back on its own. Add a trailing odd argument as the default — =SWITCH(B2,92,"Top",78,"Mid","Other") — where "Other" catches everything unmatched. Note the contrast: IF with its else omitted returns FALSE, not #N/A (both measured).
Which Excel versions support SWITCH?
SWITCH arrived in Excel 2019 and Microsoft 365 (and Google Sheets). It is NOT in Excel 2016 or earlier — those files show #NAME?. IF works in every version back to the earliest spreadsheets, which is why it remains the portable choice for files you share widely.