🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel IFS and SWITCH Functions — Multi-Way Logic Without the Nested-IF Pyramid

|

Excel IFS and SWITCH Functions — Multi-Way Logic Without the Nested-IF Pyramid

TL;DR — IFS replaces a stack of nested IFs: it checks conditions top to bottom and returns the first one that's true — =IFS(test1, val1, test2, val2, …). Two traps cause almost every IFS bug. (1) There's no built-in else — if no condition matches, IFS returns #N/A; always finish with TRUE, default to catch the rest. (2) First match wins — order your conditions narrowest/highest first, or a broad test up top swallows everything below it. SWITCH is the sibling for matching one value against a fixed list of cases (exact equality, not ranges). Both need Excel 2019 or 365.

=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")
=SWITCH(C2, "N", "North", "S", "South", "E", "East", "Unknown")

The moment a decision has more than two outcomes, a nested IF starts turning into a pyramid of brackets. IFS and SWITCH are the cure — but each has one sharp edge that produces a silent wrong answer if you don't know it. This guide leads with the mental model for each, then the two rules that keep them correct.

What you'll learn

  • The mental model: IFS is a checklist, SWITCH is a switchboard
  • IFS trap #1: there is no else — a missed default returns #N/A
  • IFS trap #2: first match wins, so condition order is the logic
  • When to reach for SWITCH instead — and why it can't do ranges
  • The judgment call: IFS vs SWITCH vs an XLOOKUP lookup table

The mental model: a checklist and a switchboard

These two functions solve different shapes of the same "more than two outcomes" problem, and naming them keeps you from misusing one for the other.

IFS is a checklist read top to bottom. Each pair is "if this condition, then this value." Excel walks down the list and stops at the first test that's true:

' Walk down: first true test wins
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")

SWITCH is a switchboard for one value. You hand it a single expression, then a list of cases; it routes to the case that equals that value:

' Match C2 against exact values
=SWITCH(C2, "N", "North", "S", "South", "Unknown")

The distinction is the whole game: IFS evaluates a different boolean test each row (great for ranges and mixed conditions), while SWITCH compares one expression against several constants (great for code-to-label mapping). Use the wrong one and you'll be fighting the function.

IFS trap #1: there is no else — and the penalty is #N/A

This is the number-one IFS bug. Unlike IF, which always has a false branch, IFS has no built-in else. If a row matches none of your conditions, IFS doesn't return blank or zero — it returns the error #N/A:

=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C")     ' score 55 -> #N/A
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")   ' score 55 -> "F"

The fix is a fixed idiom: end every IFS with TRUE, default. Because TRUE is always true, it's the catch-all that runs when nothing above matched — exactly what else does in other languages. If you ever see #N/A from an IFS, you forgot the TRUE line. Make it muscle memory: the last pair is always your fallback.

IFS trap #2: first match wins, so order is the logic

IFS returns the value for the first true test and never looks further. That makes the order of your conditions the actual logic — and getting it backwards fails silently:

' WRONG order — the broad test is first, so it catches everyone
=IFS(B2>=70, "C", B2>=80, "B", B2>=90, "A", TRUE, "F")
' A score of 95 is >=70, so it returns "C" and stops. Every high score becomes "C".

' RIGHT order — narrowest/highest band first
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")

There's no error here — just wrong grades that look plausible until someone checks. The rule for any ordered bands (scores, tiers, age brackets, discount thresholds) is the same: list from the most restrictive to the least. When your conditions overlap, the first one wins, so the tightest test has to come first or it never gets a turn.

SWITCH: exact matches, a cleaner default, and one thing it can't do

SWITCH shines when you're mapping a single value to a label and the nested-IF version would just repeat the same cell over and over:

' Nested IF — repeats C2 every branch
=IF(C2="N","North", IF(C2="S","South", IF(C2="E","East","Unknown")))

' SWITCH — say C2 once
=SWITCH(C2, "N","North", "S","South", "E","East", "Unknown")

Note the default: if SWITCH has an odd number of arguments after the expression, the last one is the fallback (here, "Unknown") — no TRUE trick needed. The hard limit to remember: SWITCH only does exact equality. It cannot test >, <, or a range. If you try to route grade bands through SWITCH, you'll fight it — that's IFS territory. (There's a SWITCH(TRUE, …) trick that emulates IFS, but if you need ranges, just use IFS and keep it readable.)

The judgment call: IFS vs SWITCH vs a lookup table

Three shapes, three tools:

  • Ranges or mixed conditions (score ≥ 90, and region = West, or flag set) → IFS. Each test is its own boolean; order narrowest first; end with TRUE.
  • One value → one label, short and fixed (N/S/E/W, a status code) → SWITCH. Exact matches, value named once, trailing default.
  • A mapping that's long, grows, or business-owned (product → price, employee → manager) → don't hard-code it in a formula at all. Put it in a two-column table and use XLOOKUP. A 30-case SWITCH is a maintenance trap; a lookup table is data anyone can edit without touching a formula.

The mistake isn't choosing IFS over SWITCH or vice versa — it's stuffing a lookup table's worth of cases into either one. The bigger and more volatile the mapping, the more it belongs in cells, not in syntax.

How ExcelMaster helps

Multi-way logic is where hand-written formulas get fragile: a forgotten TRUE default, bands in the wrong order, a 20-case SWITCH that should have been a lookup. ExcelMaster reads the intent — grade these scores, A through F or map each code to its region — and produces the right structure: ordered IFS with a catch-all, a tidy SWITCH, or a lookup table when the mapping has outgrown a formula. You describe the rule; it avoids the silent #N/A and the order trap.

Frequently asked questions

Why does my IFS return #N/A?

No condition matched and you have no catch-all. IFS has no built-in else, so an unmatched row returns #N/A. End the formula with TRUE, default_value — for example …, TRUE, "Other") — to handle everything that falls through.

What's the difference between IFS and SWITCH?

IFS evaluates a separate boolean test for each case, so it handles ranges and mixed conditions. SWITCH compares one expression against a list of exact values, so it's for value-to-label mapping. Use IFS for >=/ranges, SWITCH for exact matches.

Does the order of conditions matter in IFS?

Yes — IFS returns the first true test and stops. For overlapping conditions like score bands, list the narrowest/highest first; otherwise a broad test at the top catches rows meant for a later branch.

How do I add a default value in SWITCH?

Put it as the final argument with no matching case before it. If the argument count after the expression is odd, the last one is the default: =SWITCH(C2, "N","North", "S","South", "Unknown").

Do IFS and SWITCH work in Excel 2016?

No. Both were added in Excel 2019 and are in Microsoft 365. In Excel 2016 or earlier, fall back to nested IF or a lookup table.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-25.

Related guides: Excel IF · Excel IFERROR · Excel XLOOKUP · Excel SUMIFS