Excel SWITCH function: replace nested IF the clean way

You open a workbook someone else built, click a cell, and the formula bar shows six IFs wrapped inside each other. The intent is a lookup — a status code maps to a label — but reading the formula takes longer than typing the label by hand. Nested IF is the wrong shape for that job. Excel added SWITCH in 2019 precisely to fix it: one expression, then value–result pairs, then an optional default. This post walks the migration from nested IF to SWITCH, shows the one workaround that lets SWITCH handle ranges, and marks the point where IFS or a real lookup table becomes the better choice.

When a nested IF stops being readable

A single IF is fine. Two nested IFs are usually fine. The trouble starts around three, and by five the formula is a puzzle even for the person who wrote it last month. The repeated cell reference is the tell — every branch re-types the same test.

Consider a support ticket sheet where column A holds a priority code and you want a plain-English label in column B. The nested version reads the same value four times, once per level, and any typo in one branch silently changes the meaning of the others.

=IF(A2="P1","Critical",
  IF(A2="P2","High",
    IF(A2="P3","Medium",
      IF(A2="P4","Low","Unknown"))))

=SWITCH(A2,
  "P1","Critical",
  "P2","High",
  "P3","Medium",
  "P4","Low",
  "Unknown")

Same output, half the visual weight. And when a P5 gets added later, the nested version needs another IF and another closing bracket; SWITCH needs one more pair.

The SWITCH syntax in one pass

SWITCH takes one expression, then alternating value and result arguments, then an optional default at the end. Microsoft documents up to 126 value-result pairs per call — the same 254-argument ceiling every Excel function inherits.

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

The expression is anything that returns a value — a cell, a text literal, a calculation. Each value is compared to that expression using exact match only. The first hit wins; SWITCH stops evaluating the rest. If nothing matches and no default is provided, SWITCH returns #N/A.

Reading a SWITCH formula out loud

The mental model that makes SWITCH click: read it as “look at X — if it’s A, return this; if it’s B, return that; otherwise, return the last thing.” That mirrors how you would describe the logic in speech. Nested IF forces you to speak in parentheses.

Rewriting a nested IF, one pair at a time

The mechanical migration is simple enough to do on paper. Every IF(X="A","result",IF(X="B",…)) becomes one value, result pair inside SWITCH. The final IF’s else-branch becomes the trailing default. Here is what the ticket-priority sheet looks like end to end.

Row 2 hits the first pair. Row 3 hits the third pair. Row 4 misses every pair and falls through to the default. If the default were removed, row 4 would return #N/A — which is often exactly what you want, because it makes unmapped codes visible instead of hidden. That decision is worth making on purpose, not by accident.

The default argument: use it or catch the error

SWITCH has one optional trailing argument with no explicit name — the default. It fires when nothing matched. The syntax is deliberately terse: you drop a single value at the end, without a paired keyword.

=SWITCH(A2,"P1","Critical","P2","High","Not set")

The last argument, "Not set", is unpaired, so SWITCH treats it as the default. Get the count wrong — one extra or missing argument — and Excel will interpret a value as a result or vice versa, quietly producing wrong labels for real inputs. Count the arguments after typing.

When a #N/A default is the right behavior — say, you want unmapped rows to stand out in a report — you can still add a friendly display without changing the SWITCH itself:

=IFERROR(SWITCH(A2,"P1","Critical","P2","High"), "Review needed")

That keeps the mapping honest inside SWITCH and pushes the fallback out one layer. Two responsibilities, two functions.

Faking range logic with SWITCH(TRUE, …)

The most common critique of SWITCH is that it only does exact matches — no >, no <, no ranges. That is technically true, and it is the reason Microsoft also ships IFS. But there is a workaround that keeps SWITCH readable when the branches are conditions rather than values: pass TRUE as the expression, and let each value slot hold a boolean test.

=SWITCH(TRUE,
  A2>=90,"A",
  A2>=80,"B",
  A2>=70,"C",
  A2>=60,"D",
  "F")

SWITCH walks the pairs in order and stops at the first one that equals TRUE. That is functionally identical to IFS, and often more compact than a nested IF because the expression column stays visually aligned. The catch: order matters. A score of 95 matches the first branch and stops; if the branches were reordered lowest-to-highest, every high score would incorrectly return “D”.

This pattern is also the answer to nested IF chains for grading and tiering problems — the classic school-report use case. The SWITCH form makes the thresholds line up like a table you can proofread column by column.

When SWITCH is the wrong tool

SWITCH shines for small, fixed maps. Once the mapping grows past twenty or so pairs, or the values live in a real dataset, a lookup formula becomes both faster and easier to maintain. Here is the honest comparison.

The rule of thumb: if the mapping ever needs to be edited by someone who does not write formulas, do not put it inside a formula. Use a two-column lookup table on a separate sheet and point XLOOKUP at it. SWITCH is for logic that belongs in the formula because it will change at the same pace as the rest of the workbook — which, for most category codes, is almost never.

Combining SWITCH with LET for very long mappings

If the expression itself is a calculation — UPPER(TRIM(A2)), say — repeating it inside SWITCH is fine, but naming it once with LET is cleaner and slightly faster. The same idea powers reusable variables inside a formula: define once, reference many times.

=LET(code, UPPER(TRIM(A2)),
  SWITCH(code, "P1","Critical", "P2","High", "P3","Medium", "P4","Low", "Unknown"))

For truly custom logic that repeats across many workbooks, wrap the SWITCH in a LAMBDA and name it so the workbook only has to see the friendly name.

A short migration checklist

Before you replace a nested IF, walk through this list once. It catches the mistakes that turn a clean SWITCH into a subtly-wrong formula.

• ✓ The comparison is exact-match, not a range — otherwise use SWITCH(TRUE, …)
• ✓ Every value appears once; no duplicates that would shadow later branches
• ✓ The default is either present, or #N/A is the deliberate signal you want
• ✓ The expression is wrapped in TRIM/UPPER if the source data is inconsistent
• ✓ The workbook will only be opened in Excel 2019 or later

Swap in SWITCH on the shortest, ugliest nested IF you own first. Ten minutes of migration usually saves an hour of debugging later, and the resulting formula reads like the sentence you would have used to describe it.