Featured image
IFS in Excel
IFS takes test and result in pairs and returns the result belonging to the first test that passes: =IFS(test1, value1, test2, value2, …). It replaces a stack of nested IFs with a flat list, which is easier to read and exactly as order-dependent. There is no otherwise argument; a final test of TRUE does that job.
=IFS(test1, value1, [test2, value2], …)
A nested IF three levels deep is a formula with brackets at the end that nobody can count. IFS is the same logic written flat: a list of pairs, read top to bottom, stopping at the first test that comes out TRUE.
What it does not change is the thing that actually goes wrong with banded logic. Because IFS stops at the first match, the order of the pairs is part of the meaning, and a list of individually correct tests in the wrong order produces a column of confidently wrong labels with no error anywhere.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The first condition. Anything that evaluates to TRUE or FALSE.
- What to return if that first condition holds. If it does, nothing after this point is evaluated at all.
- optional
- Further pairs, checked in order. Excel allows up to 127 of them, which is about 124 more than anybody should write.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Three bands, highest threshold first
=IFS(C4>=15, "High", C4>=8, "Mid", TRUE, "Low")Returns High
Chen's 19 passes the first test, so High comes back and the remaining pairs are never looked at. Reading downwards, the formula says: 15 or more is High, otherwise 8 or more is Mid, otherwise Low.
A value that falls through to the catch-all
=IFS(C5>=15, "High", C5>=8, "Mid", TRUE, "Low")Returns Low
Dara's 4 fails both real tests. The final pair is the literal TRUE, which always passes, so Low is what is left. That is how IFS expresses otherwise, and leaving it off is the cause of most IFS errors.
The same tests in the wrong order
=IFS(C4>=8, "Mid", C4>=15, "High", TRUE, "Low")Returns Mid
Nothing here is misspelled and every comparison is correct, yet Chen's 19 is labelled Mid. The >= 8 test catches it first, and the High branch can never be reached by anybody. This is the single most common IFS fault.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Getting the order right
There is a rule that works every time and does not require thinking about the data: with >= tests, put the highest threshold first; with <= tests, put the lowest first. Each test then only ever sees the rows the ones above it rejected.
- 1Write the bands out in plain words before touching the formula, highest to lowest.
- 2Turn each line into a test and result pair, in that order.
- 3Finish with TRUE and the fallback, so no row can fall off the end.
- 4Check the output column contains every label. A band that never appears is being swallowed by a broader test above it.
- 5Check the boundary rows on purpose. Exactly 15 and exactly 8 should land where the written rules say they do.
IFS, nested IF, or a lookup table
- Two outcomes: use IF. IFS for a single pair is longer and says less.
- Three to five outcomes: IFS is clearer than nesting, because the tests line up vertically instead of burrowing into each other's false branches.
- More than about five, or thresholds that change: stop putting them in a formula. Put the bands in a small table on the sheet and use VLOOKUP in approximate-match mode, or INDEX with MATCH. Thresholds that live in cells can be changed by somebody who does not write formulas.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=IFS(C5>=15, "High", C5>=8, "Mid")WhyEvery test failed and there was no catch-all. Dara's 4 is neither 15 or more nor 8 or more, and IFS has nothing left to return.
FixAdd a final pair of TRUE and whatever the leftover case should be: =IFS(C5>=15, "High", C5>=8, "Mid", TRUE, "Low"). An #N/A from IFS almost always means a gap in the bands rather than a broken formula.
=IFS(C4>=15, High, TRUE, Low)WhyText results without quotation marks. Excel reads High as a name it does not have.
FixQuote literal text: "High". Numbers are never quoted.
What goes wrong
These are the mistakes that come up again and again with IFS. Each one is reasonable. That is why people make it.
Ordering the pairs from the broadest test to the narrowest.
WhyIt reads naturally and each test on its own is correct.
InsteadHighest threshold first with >=. Then confirm every label actually appears in the finished column.
Leaving off the final TRUE.
WhyEvery row in the test data happens to match one of the bands.
InsteadAdd it. The row that matches nothing is the one that arrives next month, and it will arrive as #N/A.
Expecting IFS to evaluate every test.
WhyThe pairs look like a list of independent rules.
InsteadIt stops at the first pass. A row can satisfy three of the tests and only the first one counts.
Common questions
What is the difference between IF and IFS?
IF handles two outcomes and has to be nested for more. IFS takes any number of test and result pairs as a flat list. Both stop at the first condition that holds, so both depend on the order of the tests.
Does IFS have an ELSE argument?
No. The convention is a final pair whose test is the literal TRUE, which always passes: =IFS(test1, a, test2, b, TRUE, "everything else").
Why does my IFS return #N/A?
No test passed. Either the bands have a gap in them or the catch-all TRUE pair is missing. Add it and the #N/A becomes the fallback value.
Which versions of Excel have IFS?
Excel 2019 and Microsoft 365. In earlier versions, nested IF does the same job.