Nested IF vs IFS vs SWITCH
Updated · Works in: Excel 2019+, Microsoft 365, Google Sheets · Every example checked before release
Short answer
Nest IFs highest bar first, =IF(x>=85,"A",IF(x>=70,"B","C")); or list conditions with IFS(…, TRUE, fallback), ending with TRUE so nothing returns #N/A. Use SWITCH(value, match, result, …, default) for exact matches.
Three or more outcomes? You can nest IFs, list conditions with IFS, or match exact values with SWITCH. They give the same results; readability is the difference.
Nested IF
Each IF's false branch holds the next IF. Test the highest bar first, or the higher outcomes are never reached.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | Grade | From | Grade | |
| 2 | Noor | A | 2 | 48 | C or below | 0 | F | |
| 3 | Mateo | B | 5 | 66 | C or below | 50 | D | |
| 4 | Hana | A | 8 | 81 | B | 60 | C | |
| 5 | Ollie | B | 3 | 55 | C or below | 70 | B | |
| 6 | Zara | A | 10 | 93 | A | 85 | A | |
| 7 | Ivan | B | 6 | 72 | B | |||
| 8 | Lea | A | 1 | 39 | C or below | |||
| 9 | Omar | B | 7 | 77 | B | |||
| 10 | Sara | A | 9 | 88 | A | |||
| 11 | Theo | B | 4 | 61 | C or below | |||
| 12 | Yuki | A | 6 | 70 | B | |||
| 13 | Bea | B | 8 | 84 | B |
IFS, with a catch-all
IFS(cond1, value1, cond2, value2, …) is flatter. If no condition is TRUE it returns #N/A, so end with TRUE, fallback. To keep text out of numeric bands, check the type first: IF(ISNUMBER(C2), IFS(C2<18, "Under 18", TRUE, "18+"), "Unknown").
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | Grade | From | Grade | |
| 2 | Noor | A | 2 | 48 | Below C | 0 | F | |
| 3 | Mateo | B | 5 | 66 | C | 50 | D | |
| 4 | Hana | A | 8 | 81 | B | 60 | C | |
| 5 | Ollie | B | 3 | 55 | Below C | 70 | B | |
| 6 | Zara | A | 10 | 93 | A | 85 | A | |
| 7 | Ivan | B | 6 | 72 | B | |||
| 8 | Lea | A | 1 | 39 | Below C | |||
| 9 | Omar | B | 7 | 77 | B | |||
| 10 | Sara | A | 9 | 88 | A | |||
| 11 | Theo | B | 4 | 61 | C | |||
| 12 | Yuki | A | 6 | 70 | B | |||
| 13 | Bea | B | 8 | 84 | B |
SWITCH for exact matches
When you compare one value against a list, SWITCH is clearest, with a default at the end.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Price | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | $12.00 |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | $5.00 |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | $12.00 |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | $30.00 | |
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | $5.00 |
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | $12.00 |
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | $5.00 |
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | $0.00 |
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | $30.00 |
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 | $5.00 |
Check yourself
One real exercise from the “Data quality” unit. Checked on our server; no account needed.Put every member in an age band: "Under 18", "18–29" or "30+", and "Unknown" when the age isn't a number. Start in F2.
- Use IFS and ISNUMBER
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Plan | Joined | Band | |
| 2 | mara@example.com | Mara | 34 | Pro | 12 Jan 2026 | |
| 3 | jonas@example.org | Jonas | 41 | Basic | 15 Jan 2026 | |
| 4 | MARA@example.com | Mara O. | 34 | Pro | 20 Jan 2026 | |
| 5 | priya@example.net | Priya | Team | 2 Feb 2026 | ||
| 6 | tomas@example.com | Tomás | n/a | basic | 9 Feb 2026 | |
| 7 | elif@example.org | Elif | 29 | Pro | 17 Feb 2026 | |
| 8 | jonas@example.org | Jonas Lind | 41 | Basic | 23 Feb 2026 | |
| 9 | kenji@example.net | Kenji | 52 | Enterprise | 3 Mar 2026 | |
| 10 | sofia@example.com | Sofia | 23 | Team | 8 Mar 2026 | |
| 11 | leo@example.org | Leo | 17 | Basic | 14 Mar 2026 |
Common mistakes
No catch-all in IFS: unmatched rows show #N/A.
Conditions in the wrong order (lowest bar first), so everything lands in the first band.
Long chains of IF for a lookup table's job: with many bands, use approximate XLOOKUP.
Questions
Which versions have IFS and SWITCH?+
Excel 2019 and later (including Microsoft 365) and Google Sheets. In older Excel, nest IFs.
Is IFS faster than nested IF?+
Not meaningfully. Choose the one that's easier to read.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.