Back to Blog

Excel SWITCH Function — Replace Nested IFs with a Flat, Readable Formula

|

Excel SWITCH Function — Replace Nested IFs with a Flat, Readable Formula

TL;DR — SWITCH(expression, value1, result1, value2, result2, …, [default]) evaluates expression once, walks the value/result pairs left to right, and returns the result for the first exact match. It's the clean way to turn one input into one of several outputs — the flat replacement for a tower of nested IFs. The one rule that governs everything: SWITCH matches on exact equality, never on > or <.

=SWITCH(A2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")
=SWITCH(WEEKDAY(A2), 1, "Weekend", 7, "Weekend", "Weekday")
=SWITCH(B2, 1, "Gold", 2, "Silver", 3, "Bronze")          ' no default -> #N/A if no match

If you've ever written =IF(A2="N","North",IF(A2="S","South",IF(A2="E",…))) and lost count of the closing parentheses, SWITCH is the function you wanted. It reads top to bottom like a table, and adding a new case is one more pair — not one more nesting level.

What you'll learn

  • The mental model: an inline routing table keyed on exact values
  • Why SWITCH does exact equality only — the line between it and IFS
  • The default argument, and the #N/A you get when you forget it
  • The SWITCH(TRUE(), …) trick for ranges — and why it's a trade-off
  • SWITCH vs IFS vs nested IF: a decision rule you can actually apply

The mental model: an inline routing table

Think of SWITCH as a small lookup table you write directly inside the formula. You hand it one value to look up (the expression), then a series of case → result pairs. Excel evaluates the expression a single time and hands back the result sitting next to the first case it equals:

=SWITCH(status, "A", "Active", "P", "Pending", "C", "Closed", "Unknown status")
'        ▲ look up this        ▲ if it equals "A", return "Active" … etc.

Everything good about SWITCH flows from this picture. It's flat — cases sit side by side, not nested inside each other, so ten cases read as easily as two. It evaluates the expression once, so SWITCH(SomeSlowFormula(), …) doesn't recompute that formula for every branch the way a nested IF chain re-tests. And the intent is obvious at a glance: this formula maps one thing to a fixed set of outcomes.

The one rule that defines SWITCH: exact equality

Here is the fact that explains every "SWITCH won't work" question: SWITCH compares the expression to each case using plain equality — the same as =. There is no operator, no range, no "greater than." A case is either equal to the expression or it isn't.

So this does not do what a beginner expects:

=SWITCH(score, ">90", "A", ">80", "B", "F")     ' WRONG — never matches

SWITCH compares score to the text ">90", which a number never equals, so you always fall through to "F". Ranges are simply not what SWITCH is for. The moment your branches are conditions (score >= 90, amount between X and Y) rather than exact values, you've reached the edge of SWITCH — that's IFS territory. Keep the division clean in your head:

  • One expression compared to a list of exact values → SWITCH
  • A list of independent conditions (including ranges) → IFS

The default argument, and the #N/A trap

SWITCH takes an optional last argument with no matching value — the default, returned when nothing matched:

=SWITCH(grade, "A", 4.0, "B", 3.0, "C", 2.0, 0)     ' 0 is the default

The way to read the argument list: pairs are value, result; if there's a lonely odd argument at the end, that's the default. Leave it off and an unmatched expression returns #N/A, not a blank:

=SWITCH(B2, 1, "Gold", 2, "Silver", 3, "Bronze")    ' B2 = 4  ->  #N/A

This is the single most common SWITCH bug: real data always contains the value you didn't plan for, and without a default it surfaces as #N/A scattered through the column. Make the default a habit — even if it's just "Other" or 0 — so unexpected inputs land somewhere you chose rather than erroring.

The SWITCH(TRUE()) trick — clever, but weigh it

There's a well-known way to make SWITCH handle ranges: set the expression to TRUE() and make each case a condition that returns TRUE or FALSE. SWITCH then returns the result of the first case that evaluates to TRUE:

=SWITCH(TRUE(),
        score>=90, "A",
        score>=80, "B",
        score>=70, "C",
        "F")

It works because score>=90 evaluates to TRUE/FALSE, and SWITCH looks for the first case equal to TRUE(). It's genuinely useful and order-dependent (first-match-wins, so list from strictest to loosest, exactly like a graded IFS).

But be honest about the trade-off: you're using an equality-matcher to fake range logic, and the next person has to decode why the expression is TRUE(). When the branches are ranges, IFS says the same thing more directly. Reach for SWITCH(TRUE()) when you're already deep in SWITCH and want one range case; prefer IFS when the whole formula is ranges.

SWITCH vs IFS vs nested IF

All three pick one result from several, so which to use isn't about ability — it's about which one states your intent most clearly:

' Nested IF — works, but parentheses pile up and intent is buried
=IF(A2="N","North",IF(A2="S","South",IF(A2="E","East","Other")))

' SWITCH — one expression, exact-value cases, flat
=SWITCH(A2, "N","North", "S","South", "E","East", "Other")

' IFS — independent conditions, especially ranges
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", TRUE,"F")

The decision rule:

  • Comparing one thing to a set of exact values? SWITCH. It's the most readable and it evaluates the expression once.
  • Testing independent conditions or ranges? IFS.
  • Still writing three-plus levels of nested IF? That's the smell — one of the two above will read better and break less when you edit it six months from now.

One caveat worth stating plainly: SWITCH arrived in Excel 2019 and Microsoft 365. On Excel 2016 or earlier it doesn't exist, and you're back to nested IF or a lookup table with VLOOKUP / INDEX & MATCH.

The judgment call

SWITCH earns its place by making intent visible. When a formula's job is "map this one value to one of these outcomes," SWITCH says exactly that — flat, ordered, easy to extend. Its narrowness (exact equality only) isn't a weakness; it's the signal that tells you when you've picked the wrong tool. If you're stuffing ">90" strings into cases or nesting SWITCH(TRUE()) five deep, the formula is telling you it wanted IFS. And if the list of cases is long, stable, and lives in your data anyway — regions, product codes, status labels — the most maintainable answer isn't SWITCH or IFS; it's a real lookup table you can edit without touching the formula. Match the tool to the shape of the decision, and the formula reads itself.

How ExcelMaster helps

Most SWITCH problems aren't syntax — they're picking SWITCH when the branches are ranges, or forgetting the default until #N/A shows up. ExcelMaster picks the right structure for you: it uses SWITCH for exact-value mapping, moves you to IFS the instant your branches become ranges, always supplies a sensible default, and suggests a lookup table when your cases are really data. Tell it "label each row North/South/East/West from the code in A" or "grade these scores," and it writes a formula that's flat, complete, and still readable when you reopen it next quarter.

Frequently asked questions

How does the SWITCH function work in Excel?

=SWITCH(expression, value1, result1, value2, result2, …, [default]) evaluates expression once, compares it to each value in order using exact equality, and returns the matching result. If nothing matches, it returns the optional default, or #N/A if you didn't provide one.

What's the difference between SWITCH and IFS?

SWITCH compares one expression against a list of exact values — ideal for mapping a code or label to an outcome. IFS evaluates a list of independent conditions, including ranges like score>=90. Use SWITCH for exact matches, IFS for conditions.

How do I add a default value in SWITCH?

Put a single extra argument at the very end, with no value paired to it: =SWITCH(A2,"N","North","S","South","Unknown"). Here "Unknown" is returned whenever A2 isn't "N" or "S". Without it, an unmatched expression returns #N/A.

Can SWITCH handle greater-than or ranges?

Not directly — it only tests exact equality. The workaround is SWITCH(TRUE(), condition1, result1, …), where each condition returns TRUE/FALSE. For range logic throughout, IFS is usually clearer.

Is SWITCH available in Excel 2016?

No. SWITCH was introduced in Excel 2019 and is in Microsoft 365. In Excel 2016 and earlier, use nested IF or a lookup table with VLOOKUP.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-08.

Related guides: Excel IFS · Excel IF · Excel CHOOSE · INDEX & MATCH