IF, IFS and Nested IF: When Each One Makes Sense

IF tests one condition and returns one result if it's true, another if it's false. IFS checks several conditions in order and returns a result for the first one that's true, without needing them nested inside each other. Nested IF stacks multiple IF functions together to handle several conditions the older way, and while it still works, it gets unreadable fast once you're past two or three levels.

I built a nested IF formula years ago to categorise sales tiers — Bronze, Silver, Gold, Platinum, based on revenue thresholds. It worked fine for a while, four levels deep, until my boss asked me to add a fifth tier called "Diamond" for a new top bracket. Adding it meant carefully counting parentheses to find exactly where to insert the new condition without breaking the closing brackets at the end, and I got it wrong twice before it worked. That formula was maybe 200 characters long and genuinely difficult to read even for me, the person who wrote it. When IFS came along, rebuilding that same formula took about a third of the time and was actually readable by someone else six months later, including me.

How does a basic IF formula work?

The structure is simple:

=IF(logical_test, value_if_true, value_if_false)

Flagging orders over $1,000 as "Priority":

=IF(C2>1000, "Priority", "Standard")

If the value in C2 is greater than 1,000, it returns "Priority." Otherwise, it returns "Standard." One condition, two possible outcomes.

How do I nest multiple IF formulas together?

Nesting means putting one IF formula inside another IF formula's false (or true) argument, so you can check a second condition if the first one doesn't apply.

Categorising sales into tiers based on revenue:

=IF(C2>=100000, "Platinum", IF(C2>=50000, "Gold", IF(C2>=20000, "Silver", "Bronze")))

Reading it in order: if revenue is 100,000 or more, "Platinum." If not, check whether it's 50,000 or more for "Gold." If not, check 20,000 or more for "Silver." Anything left over gets "Bronze."

This works, but notice how each additional tier adds another layer of nested parentheses. Go much past three or four levels and it becomes genuinely hard to read, edit, or trust at a glance — which is exactly the trap I fell into with my five-tier version.

Why is IFS usually better than nested IF?

IFS checks a list of conditions in order and returns the result for whichever one is true first, without the nested parentheses structure at all.

=IFS(condition1, value1, condition2, value2, ...)

The same sales tier example, rebuilt with IFS:

=IFS(C2>=100000, "Platinum", C2>=50000, "Gold", C2>=20000, "Silver", C2<20000, "Bronze")

Each condition-and-result sits as its own clear pair, with no nesting and no parenthesis-counting. Adding a new tier is just adding one more pair in the list, rather than carefully inserting a new IF in the middle of an existing formula and matching brackets correctly.

	Nested IF	IFS
Readability past 3 conditions	Poor	Good
Adding a new condition later	Requires careful bracket editing	Add one more pair
Available in	All Excel versions	Microsoft 365 and Excel 2019 onward
Can include a catch-all "else"	Yes, as the final argument	Use TRUE as a final condition

How do I add a catch-all "otherwise" result to IFS?

Unlike nested IF, which naturally ends with a final "else" value, IFS needs an explicit catch-all condition, or it returns an #N/A error if nothing matches.

=IFS(C2>=100000, "Platinum", C2>=50000, "Gold", C2>=20000, "Silver", TRUE, "Bronze")

Using TRUE as the very last condition means "if none of the above matched, use this result" — it always evaluates as true, so it catches everything left over. This trips up a lot of people the first time they switch from nested IF to IFS, since it isn't obvious IFS doesn't have a built-in catch-all the way nested IF does with its final argument.

Why does my IF formula return the wrong result even though the logic looks right?

A few common causes, beyond a genuine logic mistake:

1. Text needs quotation marks, numbers don't. IF(A2="Yes", ...) needs the quotes; IF(A2=1, ...) doesn't.
2. Comparing text with different capitalisation or spacing. Excel's equals sign ignores capitalisation by default, but trailing spaces will still break a match.
3. Order of conditions in nested IF or IFS matters. If an earlier condition is broader than intended, it can catch cases meant for a later condition. In the sales tier example, checking C2>=20000 before C2>=50000 would wrongly classify Gold-level sales as Silver, since the first true condition wins.
4. Comparing a date stored as text against a real date. This looks identical on screen but fails silently in the comparison.

Should I combine IF with AND or OR?

Yes, for conditions that need more than one thing to be true (or any one of several) before returning a result.

Flagging an order as "Priority" only if it's both over $1,000 and from a repeat customer:

=IF(AND(C2>1000, D2="Repeat"), "Priority", "Standard")

Flagging as "Priority" if either condition is true:

=IF(OR(C2>1000, D2="Repeat"), "Priority", "Standard")

AND requires every condition inside it to be true; OR only needs one. This is a cleaner way to handle multiple conditions within a single true/false test than trying to nest IFs to achieve the same thing.

FAQ

Can I use IFS with more than 5 or 6 conditions? Technically yes, up to 127 pairs, but past six or seven conditions it's worth stepping back and asking whether a lookup table with XLOOKUP would be clearer and easier to maintain than a long chain of conditions.

What's the difference between IF and IFERROR? IF tests a logical condition you define yourself. IFERROR specifically checks whether a formula produces an error (like #N/A or #DIV/0!) and lets you substitute something friendlier instead — they solve different problems and are often used together.

Does IFS work in Excel 2016? No. IFS was introduced in Excel 2019 and is also available in Microsoft 365. On Excel 2016 or earlier, you'll need to use nested IF instead.

Why does IFS give me an #N/A error? This means none of your listed conditions were true, and you didn't include a catch-all. Add TRUE as a final condition with your desired default result, as shown above.

Can I nest IFS inside another IFS for very complex logic? Technically possible, but if you're nesting IFS formulas, it's usually a sign the underlying logic would be better handled with a lookup table, SWITCH, or breaking the problem into a helper column rather than one increasingly complicated formula.

Can I combine IF with SUMIF or COUNTIF?
Yes — a common pattern is using IF to decide which SUMIF or COUNTIF result to show, or wrapping a SUMIF inside an IF to add a fallback message when nothing matches. If you haven't already, my post on SUMIF, COUNTIF and SUMIFS covers those in detail and pairs well with what's here.