IF vs SWITCH: comparisons or exact matches
SWITCH tests ONE expression against a list of exact values — perfect for mapping codes to labels. IF (and its flat sibling IFS) handle ranges and comparisons that SWITCH can't express. Use SWITCH when you're matching equals; reach for IF/IFS the moment you need greater-than.
The differences at a glance
| IF | SWITCH | IFS | |
|---|---|---|---|
| Tests | Any condition (>, <, =, AND/OR) | One expression equal to each case value | |
| Ranges (score >= 80) | Yes | No — exact matches only | |
| Repetition | Restates the expression each branch | States the expression ONCE, then value/result pairs | |
| Default / else | The false argument | A final lone argument (odd one out) is the default | |
| Availability | Universal | Excel 2019+, Google Sheets, LibreOffice (verified) |
Which should you use?
- SWITCH — Mapping a value to outputs by exact match: status codes to labels, month numbers to names, department IDs to managers. =SWITCH(A2,1,"Low",2,"Med",3,"High","?") is cleaner than nested IFs and names the expression once.
- IF / IFS — Anything involving ranges, comparisons, or combined conditions (grades by score band, tiered pricing). SWITCH(TRUE, cond1, val1, ...) can fake ranges, but IFS reads better for that.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute identically across every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. SWITCH needs Excel 2019+ (it's in Google Sheets and LibreOffice throughout our tested range); in older Excel, nested IF or a lookup table is the fallback. For the IF-vs-IFS choice specifically, see that comparison. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 11 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| SWITCH (exact-match mapping) | =SWITCH(A2,1,"Low",2,"Med",3,"High","Unknown") |
| IF (a range test) | =IF(A2>=80,"Pass","Fail") |
| SWITCH(TRUE,...) faking ranges | =SWITCH(TRUE,A2>=80,"A",A2>=70,"B","C") |
Full per-version details on each function page: IF · SWITCH · IFS.