Replace Nested IFs with a Clean SWITCH

Excel & Google Sheets

How it works

SWITCH takes the value once, then reads match/result pairs left to right: if B2 is L it returns Lead, Q returns Qualified, W returns Won. The lone final argument is the default — anything unmatched shows Unknown instead of erroring. Leave the default off and an unmatched value returns #N/A, so include one whenever the data can contain surprises. SWITCH only does exact matches; for range tests like B2>=90, use IFS, which takes condition/result pairs instead. SWITCH works in Excel 2019 and newer, and in any Google Sheets.

B2

The value to test — written once, unlike nested IFs which repeat it in every branch.

"L","Lead"

A match/result pair: if the value is exactly L, return Lead. Add as many pairs as you need.

"Unknown"

A final unpaired argument is the default. Without it, anything unmatched returns #N/A.

When to use it

Use it to translate codes into labels — deal stages, order-status letters, shipping-method codes, region abbreviations — anywhere one value maps to one label by exact match.

Common mistakes

• ✕No default on messy data.

  ✓A stage typed as "X" returns #N/A when there's no default. End with a catch-all: …,"W","Won","Unknown").

• ✕Using SWITCH for ranges.

  ✓=SWITCH(B2>=90,…) compares exact values, not conditions. For thresholds use =IFS(B2>=90,"High",B2>=50,"Medium",TRUE,"Low").

• ✕#NAME? in Excel 2016 or earlier.

  ✓SWITCH arrived in Excel 2019. On older versions, fall back to nested IFs or a small VLOOKUP mapping table.