formulastreak

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.

Exam results · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = C or below
ABCDEFGH
1StudentClassHoursScoreGradeFromGrade
2NoorA248C or below0F
3MateoB566C or below50D
4HanaA881B60C
5OllieB355C or below70B
6ZaraA1093A85A
7IvanB672B
8LeaA139C or below
9OmarB777B
10SaraA988A
11TheoB461C or below
12YukiA670B
13BeaB884B

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").

Exam results · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = Below C
ABCDEFGH
1StudentClassHoursScoreGradeFromGrade
2NoorA248Below C0F
3MateoB566C50D
4HanaA881B60C
5OllieB355Below C70B
6ZaraA1093A85A
7IvanB672B
8LeaA139Below C
9OmarB777B
10SaraA988A
11TheoB461C
12YukiA670B
13BeaB884B

SWITCH for exact matches

When you compare one value against a list, SWITCH is clearest, with a default at the end.

Member sign-ups · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = $12.00
ABCDEF
1EmailNameAgePlanJoinedPrice
2mara@example.comMara34Pro12 Jan 2026$12.00
3jonas@example.orgJonas41Basic15 Jan 2026$5.00
4MARA@example.comMara O.34Pro20 Jan 2026$12.00
5priya@example.netPriyaTeam2 Feb 2026$30.00
6tomas@example.comTomásn/abasic9 Feb 2026$5.00
7elif@example.orgElif29Pro17 Feb 2026$12.00
8jonas@example.orgJonas Lind41Basic23 Feb 2026$5.00
9kenji@example.netKenji52Enterprise3 Mar 2026$0.00
10sofia@example.comSofia23Team8 Mar 2026$30.00
11leo@example.orgLeo17Basic14 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
Select F2 and type = to start its formula (in the cell or the formula bar)
ABCDEF
1EmailNameAgePlanJoinedBand
2mara@example.comMara34Pro12 Jan 2026
3jonas@example.orgJonas41Basic15 Jan 2026
4MARA@example.comMara O.34Pro20 Jan 2026
5priya@example.netPriyaTeam2 Feb 2026
6tomas@example.comTomásn/abasic9 Feb 2026
7elif@example.orgElif29Pro17 Feb 2026
8jonas@example.orgJonas Lind41Basic23 Feb 2026
9kenji@example.netKenji52Enterprise3 Mar 2026
10sofia@example.comSofia23Team8 Mar 2026
11leo@example.orgLeo17Basic14 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.

Practise it now