Syntax, a live grading playground, the first-TRUE-wins order rule, the #N/A default trap and its TRUE() fix, and a sourced error library: branch across many conditions without a nested-IF pyramid.
IFS checks conditions in order and returns the value paired with the first TRUE one — a flat replacement for a pyramid of nested IFs. Pairs are evaluated left to right; the first match wins and the rest are ignored.
Many conditions, one winner: IFS returns the value for the first TRUE test. The gallery below was measured live in the HyperFormula engine — load any row into the playground to reproduce it.
| score | high→low order | result |
|---|---|---|
| 92 | >=90,>=60,TRUE() | "A" |
| 74 | >=90,>=60,TRUE() | "B" |
| 58 | >=90,>=60,TRUE() | "C" |
| 92 | >=60,>=90 (low first) | "B" ✗ |
Measured: with the low threshold listed first, a 92 matches >=60 and returns "B" — the >=90 band is never reached. List bands high→low.
IFS has no built-in else. When every condition is FALSE it returns #N/A. A final TRUE(),"default" pair is the catch-all.
=IFS(B6>=90,"A",B6>=60,"B")#N/A=IFS(B6>=90,"A",B6>=60,"B",TRUE(),"C")"C"Both measured on a score of 50. The only difference is the TRUE() tail — that one pair turns an error into a clean default.
| IFS | Nested IF | SWITCH | |
|---|---|---|---|
| Max | 127 pairs | 64 levels | 126 values |
| Shape | flat | pyramid | flat |
| Default | add TRUE() | built-in | built-in |
| Best for | range bands | 1–2 branches | exact match |
Rule of thumb: ≤2 branches → IF; 3+ ordered range bands → IFS with a TRUE() tail; exact-value matching → SWITCH.
Edit the score data or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to see the order trap, the #N/A default, AND between-ranges and the text-threshold bug in action.
10 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
IFS is modern (Excel 2019+/365). Older Excel returns #NAME? — here's where it lands across eight platforms and the nested-IF fallback.
| Platform | Support | Notes | Gotcha |
|---|
IFS is the flat-branching workhorse. The full course walks the decision toolkit end to end — IF & SWITCH, AND/OR/XOR, the IS* tests, and how to compose them with lookups without the order, default and blank-cell traps.