Excel Nested IF Statements: How to Write Them (and When Not To)

A nested IF statement in Excel is an IF function placed inside another IF, usually in the value_if_false argument, so Excel can test several conditions in order: =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F")))). Excel checks each test from left to right and returns the result of the first one that is TRUE, so getting the order of conditions right matters more than anything else.

Below is how to build one without errors, the mistakes that trip people up, and the cleaner alternatives Microsoft recommends for anything beyond a few levels. Function availability notes are accurate as of October 2026 and may change as Microsoft updates Excel.

How a nested IF works

A single IF has three arguments:

=IF(logical_test, value_if_true, value_if_false)

To test more than one condition, you replace value_if_false (or sometimes value_if_true) with another complete IF function. Microsoft’s nested functions guide describes this as using a function as one of the arguments of another function.

Take Microsoft’s grading example from its nested IF pitfalls article:

=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

Read it as plain English:

1. If the score in D2 is greater than 89, return “A”.
2. Otherwise, if it is greater than 79, return “B”.
3. Otherwise, if it is greater than 69, return “C”.
4. Otherwise, if it is greater than 59, return “D”.
5. Otherwise, return “F”.

With this formula, a score of 90 returns A, 78 returns C, and 45 returns F. As soon as one test is TRUE, Excel stops and ignores the rest of the chain.

Build a nested IF step by step

The safest way to write one is from the outside in, testing as you go.

1. Write the conditions down first. List every band or rule and the result it should produce, in order. For number ranges, pick one direction (highest to lowest is common) and stick to it.
2. Start with a single IF. Type =IF(D2>89,"A","F") and confirm it works for a few test values.
3. Replace the final value with the next IF. Swap "F" for IF(D2>79,"B","F"), giving =IF(D2>89,"A",IF(D2>79,"B","F")).
4. Repeat for each remaining condition. Each time, the last fallback value moves to the newest, innermost IF.
5. Close every parenthesis. Each IF needs its own closing bracket, so four IFs end with )))). Excel colors matching parentheses while you edit a formula, which helps you see which bracket belongs to which function.
6. Test the boundaries. Try values exactly on each edge (89, 90, 79, 80 and so on) plus one value well above and below the range.

Common mistakes to avoid

Conditions in the wrong order. This is the big one. If you write =IF(D2>59,"D",IF(D2>89,"A",...)), a score of 95 returns “D”, because 95 is greater than 59 and Excel never reaches the A test. Ordered range tests prevent this.

Missing or extra parentheses. Every function needs an opening and closing parenthesis, and a misplaced one can change which IF a value belongs to. Use the color matching and the step-by-step approach above.

Numbers stored as text. Writing "90" in quotes makes it text, and comparisons against text don’t behave like numeric comparisons. Keep numbers unquoted and quote only text results.

Hardcoded thresholds. If the cut-off for an A changes, you have to edit every copy of the formula. A lookup table (covered below) keeps thresholds in cells where anyone can see and change them.

Going too deep. Microsoft states that Excel allows up to 64 nested IF functions, but says plainly that it is “not at all advisable” and that long chains are very difficult to maintain. Microsoft even shows a 12-level plus/minus grading formula as an example of something that works but invites errors that are hard to spot.

Better alternatives to deep nesting

Once you pass three or four levels, one of these is usually cleaner.

IFS: one function, no nesting

The IFS function takes pairs of conditions and results and returns the result for the first TRUE condition. Microsoft’s equivalent of the grade formula is:

=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

Two things to know:

• IFS has no built-in “otherwise”. If no condition is TRUE it returns #N/A, so Microsoft recommends adding TRUE as the final condition to set a default, as in the example.
• If a condition evaluates to something other than TRUE or FALSE, IFS returns #VALUE!.

On availability, Microsoft’s current IFS page lists Excel 2019 and Office 2019 on Windows and Mac, plus Microsoft 365. Some older Microsoft pages say IFS needs a Microsoft 365 subscription, so if colleagues use older versions, check before relying on it.

SWITCH: matching exact values

SWITCH compares one expression against a list of values and returns the result for the first match:

=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")

The last argument, which has no matching result, is the default. Without a default, SWITCH returns #N/A when nothing matches. Microsoft notes you can use up to 126 value/result pairs. SWITCH is a good replacement for nested IFs that check A2="N", A2="S" and so on, but it matches exact values, so it’s not the natural fit for ranges like test scores.

A lookup table: the most maintainable option

For bands of numbers, both Office Watch and Ablebits recommend replacing the nested IF with a small lookup table. Put the lower bound of each band in one column and the result next to it:

Then, with the table in F2:G6:

1. VLOOKUP (works in every version): =VLOOKUP(D2,$F$2:$G$6,2,TRUE). The TRUE means approximate match, which finds the largest value less than or equal to the score. The first column must be sorted in ascending order or the results will be wrong.
2. XLOOKUP: =XLOOKUP(D2,$F$2:$F$6,$G$2:$G$6,,-1). A match_mode of -1 tells XLOOKUP to return an exact match or, if there isn’t one, the next smaller item. XLOOKUP isn’t in older versions of Excel, so check the “Applies to” list on Microsoft’s page against the versions your team uses.

One subtle difference: the IF formula uses “greater than 89”, while the table uses “90 or more”. For whole-number scores they give identical results, but a score of 89.5 would get an A from the IF and a B from the table. Decide which rule you actually want and set the thresholds to match.

If a score could fall outside the table (for example a negative number), wrapping the lookup in IFERROR lets you show a clear message instead of #N/A, as Ablebits suggests.

Which should you use?

• Two to four conditions, shared with people on any Excel version: a nested IF is fine.
• Many conditions and everyone has Excel 2019 or Microsoft 365: IFS reads better and avoids parenthesis counting.
• Matching codes or labels to outputs: SWITCH.
• Grades, pricing tiers, commission bands or anything whose thresholds might change: a lookup table, every time.

The real long-term test is whether someone else can open the workbook in six months and understand the logic. A short nested IF passes that test; a 12-level one rarely does.