IF vs IFS: when to stop nesting
IFS exists to kill the nested-IF pyramid: =IF(a,x,IF(b,y,IF(c,z,...))) becomes one flat list of condition/value pairs. The catch: IFS has no built-in "else", and it doesn't exist in old Excel.
The differences at a glance
| IF | IFS | |
|---|---|---|
| Multiple conditions | By nesting (readable to ~3 levels) | Flat condition/value pairs |
| Default / else branch | Yes — the 3rd argument | No — end with TRUE, <default> as the last pair |
| What happens with no match | Returns the else value | #N/A error |
| Evaluation | Stops at the first true condition | Same — first true pair wins, order matters |
| Availability | Universal | Excel 2019+ / Sheets / LibreOffice (all versions we test) |
Which should you use?
- IF — One or two conditions, you need an else-branch, or the file must open in Excel 2016 or older.
- IFS — Three or more tiers (grades, pricing bands, status labels) on modern versions — write pairs in priority order and end with TRUE,"default" as a catch-all.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice release we test and in Google Sheets. IFS needs Excel 2019 or newer — in older Excel it's #NAME?, so files shared with legacy-Office users should stick to nested IF (or SWITCH for equality tests, same version rules as IFS).
Example formulas
| Nested IF (with else) | =IF(A2>=90,"A",IF(A2>=80,"B","C")) |
| IFS equivalent (TRUE = else) | =IFS(A2>=90,"A",A2>=80,"B",TRUE,"C") |
Full per-version details on each function page: IF · IFS.