Nested IF in Excel: Grades, Bands and Tiers

A nested IF Excel formula is an IF with another IF inside it. One IF can only choose between two results; nesting lets a single formula pick from three, four or more, such as letter grades, commission tiers or delivery bands. This guide shows the pattern, the one rule that makes it work, and when to switch to IFS or a lookup table instead.

What is a nested IF in Excel?#

A nested IF puts a second IF in the value_if_false slot of the first. A function inside another function is called "nested". The shape for three results is:

=IF(test1, result1, IF(test2, result2, result3))

Excel reads it like a sentence: if test1 is TRUE, show result1. Otherwise, if test2 is TRUE, show result2. Otherwise, show result3. Each extra IF adds one more possible result. If plain IF is still new, read the Excel IF function explained simply first.

How to write a nested IF Excel formula for grades#

Build it one band at a time, from the top grade down. The grade rules here are: 90 or more is A, 80 or more is B, 70 or more is C, and anything lower is F.

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))

Notice what each part does:

• B2>=90,"A" is the first test and its result.
• The second IF sits where the first IF's FALSE result would go. Only scores below 90 reach it.
• "F" is the final FALSE result. It catches everything that failed all three tests.
• ))) closes the three IFs. Count your IFs and add the same number of closing brackets.

Why does the order of the tests matter?#

Excel checks the tests from left to right and stops at the first TRUE one. Put the tests in the wrong order and higher scores fall into a lower band:

=IF(B2>=70,"C",IF(B2>=80,"B",IF(B2>=90,"A","F")))

With this formula, Ana's 94 gets a C. 94 is at least 70, so the first test is TRUE and Excel never looks at the A test. There's no error message, just a wrong answer, which is why this mistake survives into real reports.

There are two safe orders:

1. Highest band first, with >=, as in the grade formula above.
2. Lowest band first, with <: =IF(B2<70,"F",IF(B2<80,"C",IF(B2<90,"B","A"))). This gives the same grades.

Pick one direction and stick to it. Mixing > and < in one formula is where gaps appear.

Nested IF example: commission tiers#

Bands work the same way with numbers. A sales team earns 8% on sales of 100000 or more, 5% on 60000 or more, 3% on 30000 or more, and nothing below that:

=IF(B2>=100000,0.08,IF(B2>=60000,0.05,IF(B2>=30000,0.03,0)))

Noah sold exactly 100000. Because the test uses >=, he gets the top rate. With > he would drop to 5%. Rates are numbers, so they don't need quotes. Type 0.08 or 8%, and type 100000 without a comma: inside a formula, a comma separates arguments.

Can a nested IF test text instead of numbers?#

Yes. Each test can compare text, as long as the text is in double quotes. Here, status codes become labels:

=IF(D2="P","Pending",IF(D2="S","Shipped",IF(D2="D","Delivered","Check code")))

For exact matches like this, the order of the tests doesn't matter, because only one code can match. The last result, "Check code", catches anything unexpected, such as a new code or a typo. Text tests ignore capital letters, so "p" matches too, but a hidden space after the code doesn't.

When you are matching one cell against a list of exact values, the SWITCH function is shorter: =SWITCH(D2,"P","Pending","S","Shipped","D","Delivered","Check code"). It needs Excel 2019 or later.

What are the most common nested IF mistakes?#

Most broken nested IFs have one of these problems:

Excel colours each pair of brackets differently while you edit, which helps you count them. You can also press Alt+Enter (Windows) or Option+Return (Mac) inside the formula bar to put each IF on its own line. Excel ignores the line breaks.

If a band formula returns an error because the input cell holds an error, wrap the input check with IFERROR, used the right way, rather than hiding errors from your own formula.

When should you use IFS or a lookup table instead?#

Use a nested IF for two to four bands in a file that must work in any Excel version. Beyond that, other tools are easier to read:

• IFS lists test and result pairs with no nesting: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F"). It needs Excel 2019, Excel 2021 or Microsoft 365. Learn it in the Excel IFS function guide, or see the full nested IF vs IFS comparison.
• A lookup table keeps the bands in cells. List the lower limits (0, 70, 80, 90) in F2:F5 and the grades in G2:G5, then use =VLOOKUP(B2,$F$2:$G$5,2,TRUE). The limits must be sorted from smallest to largest. When the bands change, you edit cells, not formulas. See how VLOOKUP's approximate match finds bands.
• Several conditions in one band, like "sales over 50000 and a rating of at least 4.5", need AND or OR inside the test. See AND, OR and NOT with IF.

The IF function guide and the IFS function guide list every argument if you need a quick reference.

Practise it#

Nested IFs feel tricky until you have built a few. Try the commission exercise on this page, then practise nested IF and IFS in a live spreadsheet. The lesson has you fix a formula with its tests in the wrong order, rewrite it with IFS, and calculate commission in one formula.