Stop nesting IFs: IFS, SWITCH and the lookup-table trick
Five nested IFs are hard to read and harder to fix. Compare three cleaner ways to turn scores into grades, from IFS to a tiny lookup table.
Lena OkaforUntangle your IFs
On this page
The nested IF we all wrote once
Grade bands are the classic case: 90+ is an A, 80+ a B and so on. With plain IF each band needs another IF inside the last one.
IFS: a flat list of tests
IFS takes condition/result pairs and returns the first one that’s true. End with TRUE as a catch-all for “everything else”.
Better: put the rules in a table
When the bands change you shouldn’t have to edit formulas. Keep the thresholds in a small table and use an approximate match: match_mode -1 finds the exact value or the next smaller one.
SWITCH, AND and OR
Use SWITCH when you’re matching exact values rather than ranges, and combine tests with AND/OR inside a single IF.
| Formula | What it does |
|---|---|
| =SWITCH(A2,"N","North","S","South","Unknown") | Maps codes to names, with a default |
| =IF(AND(B2>=60,C2="Yes"),"Pass","Fail") | Pass only if both conditions hold |
| =IF(OR(B2="VIP",C2>5000),"Priority","") | Flag if either condition holds |
| =IF(NOT(ISBLANK(A2)),"Done","") | React only when a cell is filled in |
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Lena Okafor
Data trainer at FluentExcel
Tom Becker
Priya Nair