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(B2<5,"Small",IF(B2<20,"Standard","Freight"))Read it from the inside out when it stops being obvious: the innermost IF only runs once the outer one has already ruled out "Small". 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 — B2 is 4.2, under the 5kg cutoff. Which of the three tiers comes back?
| A | B | C | |
|---|---|---|---|
| 1 | Shipment | Weight (kg) | Tier |
| 2 | SG-1042 | 4.2 | =IF(B2<5,"Small",IF(B2<20,"Standard","Freight")) |
| 3 | SG-1043 | 12.8 | |
| 4 | SG-1044 | 0.6 | |
| 5 | SG-1045 | 21.5 |
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.