The Excel IFS function, explained interactively.
IFS walks a list of (test, value) pairs top to bottom and returns the value for the first test that evaluates TRUE. It’s the modern, flat replacement for deeply nested IF(IF(IF(…))) — the logic reads linearly, the formula bar stays sane, and adding a fifth tier is a copy-paste instead of a parenthesis audit.
How to use IFS
- Type
=IFS(and supply the first test — a boolean expression, usually a comparison on a cell reference (A2>=90). - Add a comma and the value to return if that test wins. Most of the time this is a literal string, number, or another formula.
- Repeat — add more
test, valuepairs in priority order. IFS stops at the first TRUE, so put tightest conditions first. - End with a
TRUE, default_valuepair. Without it, rows that fail every test return#N/A. The literalTRUEalways fires, acting as the else branch.
In the demo below, hover the cards to see which pair each part of the formula represents. Rows with scores in the 60s fall through to the TRUE fallback — that’s the cascade working as designed.
(test, value) pairs top to bottom and returns the value for the first test that evaluates TRUE. Replaces deeply nested IF(IF(IF(…))) with a single flat call.#N/A. Avoid that by ending with TRUE, default_value — the literal TRUE always fires and acts as the “else” branch.| A | B | C | |
|---|---|---|---|
| 1 | Score | Grade | Tier that fired |
| 2 | 95 | A | A2>=90 |
| 3 | 82 | B | A2>=80 |
| 4 | 71 | C | A2>=70 |
| 5 | 68 | F | TRUE |
| 6 | 55 | F | TRUE |
| 7 | 88 | B | A2>=80 |
| 8 | 93 | A | A2>=90 |
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F"). Each row fires the first tier whose test is TRUE. Column C shows which pair won — note that scores in the 60s (Dev, Eva) fall through to the TRUE fallback because no threshold above is met.IFS syntax and arguments
Variadic — any number of (test, value) pairs, up to 127. See Microsoft’s official IFS reference for the canonical specification.
A2>=90 or B2="Platinum", but can be any formula that returns TRUE/FALSE. The last test is typically the literal TRUE acting as a catch-all.logical_testN is the first TRUE in the cascade. Can be a literal, a cell reference, or a nested formula. All values must be compatible types for downstream formulas to work.TRUE, default_value. Without it, IFS returns #N/A when nothing matches. Most IFS formulas in production include a fallback; treat it as mandatory unless you explicitly want #N/A for unmatched rows.IFS examples
Four patterns that cover most real-world IFS use.
Example 1: IFS — grade letters from score thresholds
The canonical IFS case — numeric thresholds cascading into discrete labels. Tighter conditions first, TRUE fallback last.
Returns "A" for 95, "F" for 55. Equivalent to IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F"))) — but IFS reads top-down with half the parentheses.
Example 2: IFS — commission tiers from sales
Mix range comparisons with different value types. IFS handles numeric thresholds and currency returns in one pass.
Each pair can have a different return formula — here the commission rate scales with the tier. Below $10,000 the TRUE fallback returns 0 (no commission), avoiding a #N/A on low performers.
Example 3: IFS — mixed conditions across columns
Unlike SWITCH, IFS lets each test check a different column or condition type. Useful for status flags where the rule depends on multiple fields.
Checks status first, then date comparisons. Earlier tests short-circuit later ones — a Cancelled row never evaluates the date tests. Replaces three nested IFs with one flat formula.
Example 4: IFS — bucketing with explicit #N/A
Sometimes you want unmatched rows to return #N/A — for example, when you’ll filter those rows out downstream. Skip the TRUE fallback.
Wrap in IFERROR to catch the #N/A and substitute a sentinel. Cleaner than writing TRUE, "?" when the point is to distinguish “known bucket” from “unrecognised input”.
Common IFS errors and fixes
Four failure modes, each with what to check and how to recover.
IFS returns #N/A
Cause: every test evaluated FALSE. IFS has no built-in default — the moment nothing matches, it returns #N/A.
Add a catch-all as the final pair: TRUE, default_value. Or wrap the whole formula with IFERROR for a more lenient fallback that also catches tests that themselves error (e.g. cell reference to a blank row).
IFS always returns the same value
Cause: pair order is wrong — a looser test sits before a tighter one, so the looser test fires first and later tests never get a chance.
For threshold cascades, put tightest conditions first. A2>=90 must come before A2>=80, or every A also triggers the B branch. IFS doesn’t sort or prioritise for you — it’s top-down, first-TRUE-wins.
IFS returns #VALUE!
Cause: odd number of arguments — a test without its paired value, or a stray extra token. IFS requires strict (test, value) pairs.
Count arguments: every test needs a value, every value needs a test. The total count should be even. If a nested formula inside a value accidentally closed the IFS call early, the argument count will look off.
IFS is slow on large ranges
Cause: IFS evaluates every test top to bottom for every cell. With 7 tiers × 100,000 rows, that’s 700,000 comparisons on every recalc.
For bucketing against a threshold table, switch to VLOOKUP with approximate match: =VLOOKUP(A2, $D$1:$E$10, 2, TRUE). It binary-searches the sorted threshold table — orders of magnitude faster than a linear cascade, and easier to maintain.
IFS vs IF, SWITCH & nested IF
Four ways to write a branching expression. Pick by how many branches you have and whether tests are equality checks or comparisons.
| Function | Best for | Fallback? | Reads like |
|---|---|---|---|
| IFS | 3–10 tiered conditions with different tests | Manual TRUE, default | Flat top-down cascade |
| IF | Single binary branch | Built-in value_if_false | True/false ternary |
| Nested IF | Anywhere older than Excel 2019 | Innermost else | Parenthesis-heavy decision tree |
| SWITCH | Equality checks against one expression | Built-in default argument | switch/case statement |
Rule of thumb: use IF for one branch, IFS for 3–10 tiered branches with mixed test types, SWITCH when you’re matching one expression against literal values. Above ~10 branches or for threshold cascades against a large set, stop writing logic inline — build a 2-column threshold table and use VLOOKUP or XLOOKUP.
IFS frequently asked questions
6.01Why does my IFS formula return #N/A?▸
Because every test evaluated FALSE. IFS has no default value — if no pair fires, it returns #N/A. Fix by adding a TRUE fallback at the end: IFS(…, TRUE, default_value). The literal TRUE always evaluates TRUE and acts as the else branch.
6.02Does the order of test/value pairs matter in IFS?▸
Yes — crucially. IFS stops at the first test that evaluates TRUE, so you must put the tightest condition first. For grade thresholds, put A2>=90 before A2>=80, or the B test will fire for every A as well and A will never appear.
6.03What’s the difference between IFS and SWITCH?▸
IFS takes boolean tests — each pair can be a completely different kind of check. SWITCH takes a single expression and matches against literal values, like a switch/case statement in code. For equality checks against one thing, SWITCH reads cleaner; for mixed range and comparison checks, IFS wins.
6.04How many pairs can IFS take?▸
Up to 127 test/value pairs — 254 arguments total. Far more than you’d realistically need; if you’re approaching that, the logic almost certainly belongs in a VLOOKUP or XLOOKUP against a threshold table instead. IFS is for 3–10-tier cascades, not hundred-way decision trees.
6.05Do I need Excel 365 to use IFS?▸
IFS was added in Excel 2019 and is in all Excel 365 releases. Google Sheets has had it since 2017. If you’re on Excel 2016 or older, fall back to nested IF — or better, to a VLOOKUP against a 2-column threshold table, which reads cleaner than either IFS or nested IF and works in every version.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.