Chapter 04 · basic · 5 min
Nested IF and IFS: Excel's CASE WHEN
SQL has CASE WHEN: a clean list of conditions, each with its own result. Excel's equivalent is a nested IF, and it gets ugly fast — three branches means two IFs wrapped inside each other, and a fourth means three. IFS fixes the readability problem, but it has its own trap.
We'll classify a small shipments grid by weight throughout this chapter.
The grid
A shipments grid: column A is a shipment ID, column B its weight in kilograms. Column C is where the tier formula goes.
Nesting one IF inside another
Three tiers — Small under 5kg, Standard under 20kg, Freight above that — means two decisions, so two IFs: the second one lives inside the "otherwise" branch of the first.
=IF(B5<5,"Small",IF(B5<20,"Standard","Freight"))Row 5, SG-1045 at 21.5kg, is the one that travels the whole formula: the outer test fails, so Excel moves into the inner IF, whose test fails too, leaving the last branch. The breakdown under the grid shows each test in turn. This is exactly CASE WHEN weight < 5 THEN 'Small' WHEN weight < 20 THEN 'Standard' ELSE 'Freight' END — same logic, no keyword for each branch.
Before you run it — B5 is 21.5, over both cutoffs. Which of the three tiers comes back?
| A | B | C | |
|---|---|---|---|
| 1 | Shipment | Weight (kg) | Tier |
| 2 | SG-1042 | 4.2 | |
| 3 | SG-1043 | 12.8 | |
| 4 | SG-1044 | 0.6 | |
| 5 | SG-1045 | 21.5 | =IF(B5<5,"Small",IF(B5<20,"Standard","Freight")) |
How Excel works it out · run it to see each value
- 1B5<5=?not Small, so the inner IF runs
- 2B5<20=?not Standard either
- 3IF(B5<20,"Standard","Freight")=?the inner IF's last branch
- 4IF(B5<5,"Small",IF(B5<20,"Standard","Freight"))=?what the outer IF hands back
IFS: one condition, one result, no nesting
IFS takes condition/result pairs and evaluates them in order, stopping at the first TRUE — the same three tiers, but flat instead of nested:
=IFS(B3<5,"Small",B3<20,"Standard",TRUE,"Freight")That last pair, TRUE,"Freight", is doing the job the bare ELSE/final IF branch did above — it's the catch-all. IFS has no built-in "otherwise"; you have to write one.
Classify SG-1043 (12.8kg)
In C3, classify SG-1043's tier using either nested IF or IFS — remember IFS needs an explicit catch-all.
| A | B | C | |
|---|---|---|---|
| 1 | Shipment | Weight (kg) | Tier |
| 2 | SG-1042 | 4.2 | |
| 3 | SG-1043 | 12.8 | |
| 4 | SG-1044 | 0.6 | |
| 5 | SG-1045 | 21.5 |
The trap: dropping the catch-all
Drop that final TRUE,"Freight" pair and try =IFS(B5<5,"Small",B5<20,"Standard") on SG-1045 at 21.5kg. Every condition evaluates to FALSE — it's not under 5, and it's not under 20 — and IFS has nothing left to fall back on. The result is #N/A, not a blank cell and not zero, which means it can silently poison a SUM or a lookup built on top of it.
This is the same shape as forgetting an ELSE in CASE WHEN: Postgres returns NULL there instead of erroring, which is its own trap, but at least a NULL doesn't blow up downstream arithmetic the way #N/A does. Always give IFS an explicit TRUE, ... as its last pair.
Which one to reach for
Two or three branches, nested IF is fine — most people can read one level of nesting without slowing down. Four or more, or a chain of very different conditions, IFS reads top to bottom like a list instead of a staircase of parentheses. Neither is wrong; nested IF is the one you'll see more of in other people's spreadsheets, so it's worth being fluent in both directions.