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 (from executed tests)
Both execute identically across every version of all three apps we test. 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.
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.