Nested IF vs IFS in Excel: The Order That Silently Relabels Your Data

1. IF, and what nesting actually costs

IF(test, value_if_true, value_if_false). One test, two outcomes. Three outcomes need a second IF, placed inside the false slot of the first.

=IF(A2>=800, "Large", IF(A2>=500, "Medium", "Small"))

Run on 880 that returns Large, which is right. It reads badly and works well, and for three bands it is perfectly fine.

What it costs is legibility per band. Each extra outcome adds a whole IF, and the closing brackets pile up at the end where they cannot be matched to anything by eye. Five bands is a formula nobody will check. Excel allows up to 64 nested IFs, which is a limit that exists to be ignored: the practical limit is about three, and it is set by the reader rather than by the software.

There is a real distinction hiding here that people trip over separately: IF labels one row at a time and builds a column, while COUNTIF and its family sweep every row and return one number. The IF family guide is the page for that difference; this one is about what to do when one row needs more than two possible labels.

2. IFS: the same logic, flat

IFS takes pairs: a condition, then the value to use if it is true, repeated as many times as you like.

=IFS(A2>=800, "Large",
     A2>=500, "Medium",
     TRUE,     "Small")

Run on 880 that returns Large, the same answer as the nested version. Every pair sits at the same level, so a fourth band is one more pair rather than one more layer, and the brackets do not accumulate.

Read it top to bottom as a set of rules and it says what it does: if it is at least 800 it is Large, otherwise if it is at least 500 it is Medium, otherwise it is Small. That readability is the entire reason to prefer it. The logic is identical.

3. The order trap, with the number it moves

Before the explanation: here are the same two conditions with the pairs swapped. The value is 880, which satisfies both. Say what comes out.

=IFS(880>=500, "Medium", 880>=800, "Large")

Medium

Medium. IFS stopped at the first condition that was true and never looked at the second. It is not choosing the closest band or the most specific rule; it is going down the list and taking the first hit.

On the sixteen-row table, that one swap does this:

                        correct order        swapped
Small                   6 orders   2,305     6 orders   2,305
Medium                  6 orders   3,875    10 orders   7,585
Large                   4 orders   3,710     0 orders       0

Every large order became a medium one. Four orders and 3,710 of revenue changed category, the total still comes to 9,890, no cell shows an error, and the Large row simply does not appear in any summary because nothing is in it. An empty band is much easier to overlook than a wrong number.

The rule that prevents it is one sentence: write the conditions in the order you would check them by hand, most restrictive first. For numeric bands that means starting from the top threshold and working down, or starting from the bottom and using the opposite comparison, but never mixing the two.

Say out loud, before writing any chain of conditions, whether a value could satisfy two of them. If it can, the order is doing real work and it needs a comment beside it. If no value can ever satisfy two, order does not matter and you have a safer formula by construction.

4. IFS has no ELSE

Before the explanation: a value of 240 meets neither >=800 nor >=500. Say what the cell shows.

=IFS(240>=800, "Large", 240>=500, "Medium")

#N/A

#N/A, because IFS ran out of conditions without finding a true one and has nothing to return. That is honest behaviour and it is not what you want in a report.

The catch-all is a final pair whose condition is literally TRUE:

=IFS(240>=800, "Large", 240>=500, "Medium", TRUE, "Small")

Small

TRUE is always true, so the last pair fires whenever nothing above it did. It is IFS's version of the final else, and leaving it off is the second most common mistake with this function after the ordering.

One judgement worth making deliberately: what the catch-all should say. "Small" claims every unmatched value belongs in the bottom band, which is right for revenue and wrong for a status code, where an unexpected value is news. "check this" as the catch-all turns the formula into its own alarm, and it is often the better default while you are still learning what is in the data.

5. Boundaries, on purpose

Every band has two edges and someone has to decide which side each edge belongs to. The formula makes that decision whether or not you thought about it, so check it with actual values.

=IF(499>=800,"Large",IF(499>=500,"Medium","Small"))     Small
=IF(500>=800,"Large",IF(500>=500,"Medium","Small"))     Medium
=IF(800>=800,"Large",IF(800>=500,"Medium","Small"))     Large

499 is Small, 500 is Medium, 800 is Large. That is what >= means, and it matches how people describe bands out loud: "500 and over is medium". Using > instead would put 500 in the Small band and would look identical in the formula.

Two failures live at these edges. A gap, where a band ends at 499 and the next begins at 501, so exactly 500 matches nothing and falls to the catch-all. An overlap, where two bands both claim 500, which is invisible because the order silently resolves it, exactly as in section three.

Testing three values per boundary catches both: one below, one exactly on it, one above. That is six values for three bands, it takes two minutes, and it is the only part of this page most people skip.

Where the boundary should sit is a separate and harder question than how to write it. A threshold chosen because it is round is a decision wearing the costume of an observation, and picking a cut-off from the data is the page for that.

6. SWITCH, for exact matches

When every branch is "does this equal that", SWITCH says it more directly. You give the expression once, then value and result pairs, and optionally a default at the end.

=SWITCH("Desk", "Desk","Furniture", "Chair","Furniture", "Lamp","Lighting", "other")
Furniture

=SWITCH("Sofa", "Desk","Furniture", "Chair","Furniture", "Lamp","Lighting", "other")
other

=SWITCH("Sofa", "Desk","Furniture", "Chair","Furniture")
#N/A

The last argument is the default, and it is recognised as a default because it stands alone with no partner. Leave it off and an unmatched value gives #N/A, the same as IFS with no catch-all.

SWITCH cannot do ranges, only equality, which is precisely why it is safer where it applies: nothing can match two branches, so nothing depends on the order. Use SWITCH for codes and categories, and IFS for anything involving greater-than.

7. The version that scales: a band table

Past about three bands, the right answer is to stop writing conditions and put the bands in cells. Two columns, sorted ascending by the lower bound:

Floor    Label
    0    Small
  500    Medium
  800    Large

Then one lookup, using approximate match, which finds the largest floor that is not above your value.

=XLOOKUP(A2, Floors, Labels, "none", -1)

Run against the same values it gives the same answers:

499   Small
500   Medium
880   Large
1100  Large

The older equivalent, for a workbook that has to open anywhere, is VLOOKUP with its last argument left as TRUE against the same sorted table, which returned Medium for 680. Both rely on the table being sorted ascending, which is the one thing to protect.

Three things change when the bands live in cells. The thresholds become visible, so anyone can see what "large" means without opening a formula. They become editable by somebody who is not you. And adding a fourth band is a new row rather than a formula edit, which means the change cannot introduce an ordering bug, because the lookup handles the ordering.

Picture the banding formula in your own most-used workbook. Could the person who asked for those bands change one of the numbers without asking you? If not, the thresholds are in the wrong place.

8. Testing every branch

A three-band formula has four ways through it: three bands and the catch-all. A five-band formula has six. Every one of those paths is a separate thing that can be wrong, and none of them is exercised until a value goes down it.

So test them on purpose, with a small block of values off to the side:

value      expected     formula
  0        Small        =the formula on this value
499        Small
500        Medium
799        Medium
800        Large
9999       Large
""         catch-all

Seven rows, thirty seconds, and it catches the ordering bug, both boundary bugs and the missing catch-all in one go. Keep the block in the workbook rather than deleting it, because the formula will be edited again and the test is what makes the next edit safe.

Then check the totals as well as the labels. Count by band and sum by band, and confirm that the counts add to your row count and the sums add to your column total:

6 + 6 + 4 = 16 rows
2,305 + 3,875 + 3,710 = 9,890

That reconciliation is what would have caught the swapped order in section three, because the Large band would have come back with zero rows against a total that still balanced.

Edge cases that break a banding formula

Six that get through.

Blank cells. A blank is treated as 0, so it lands in the bottom band rather than being flagged. Test for it first: IFS(A2="", "missing", ...).

Text where a number should be. A comparison between text and a number does not error, it just comes out false, so those rows quietly reach the catch-all. Check the column type before trusting the bands.

Negative values. A refund of −200 satisfies none of the greater-than conditions and lands in the catch-all labelled Small, which is arguably wrong. Decide what a negative means before the formula decides for you.

An unsorted band table. Approximate-match lookups assume ascending order and return a confident wrong answer if the rows get sorted by label instead. Protect the sort, or note it beside the table.

Thresholds copied into three different formulas. Somebody updates one of them. Now the report and the chart disagree and both look fine. One table, referenced everywhere, removes the possibility.

Bands that changed last quarter. Old rows keep their old labels if the label was pasted as a value rather than left as a formula, so the same revenue is Medium in March and Large in June. Keep labels as formulas, or record which version of the bands a snapshot used.