A nested IF is an IF inside another IF, used when there are more than two possible results. =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) gives an A for 90 or more, a B for 80 to 89, a C for 70 to 79, and an F below 70.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 94 | A |
| 3 | Ben | 81 | B |
| 4 | Chloe | 70 | C |
| 5 | Dan | 65 | F |
| 6 | Eve | 88 | B |
| 7 | Finn | 90 | A |
Click C2 and look at the formula bar: three IF functions, three closing brackets at the end. Change Dan's score in B5 to 75 and his grade turns from F to C.
How a nested IF is read
Excel reads the formula from the start and stops at the first test that is TRUE:
=IF(B2>=90, "A",
IF(B2>=80, "B",
IF(B2>=70, "C",
"F")))
- Is the score 90 or more? Then A, and nothing else is checked.
- Otherwise, is it 80 or more? Then B. This test does not need to say "and below 90", because a score of 90 or more never reaches it.
- Otherwise, is it 70 or more? Then C.
- Otherwise F, the value_if_false of the last IF.
Each inner IF sits in the value_if_false slot of the one before it. Excel allows up to 64 levels, but a formula with more than four or five is hard to check by eye. Excel accepts line breaks inside a formula, so you can lay out a long one like this in the formula bar: press Alt+Enter (Windows) or Control+Option+Return (Mac) before each IF.
Why the order of conditions matters
Because Excel stops at the first TRUE test, the thresholds must go from the highest to the lowest when you use >=. Column D has the same three tests in the opposite order:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Right order | Wrong order |
| 2 | Ana | 94 | A | C |
| 3 | Ben | 81 | B | C |
| 4 | Chloe | 70 | C | C |
| 5 | Dan | 65 | F | F |
| 6 | Eve | 88 | B | C |
In column D everyone with 70 or more gets a C: a score of 94 passes the first test, B2>=70, and the B and A tests are never reached. If you prefer to start with the lowest band, flip the operators: =IF(B2<70,"F",IF(B2<80,"C",IF(B2<90,"B","A"))) gives the same grades as column C.
Nested IF with text
The tests can compare text too. Here the delivery fee depends on the region, and every region not named gets the last value:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Fee |
| 2 | 1001 | North | $5.00 |
| 3 | 1002 | South | $7.00 |
| 4 | 1003 | West | $9.00 |
| 5 | 1004 | East | $6.00 |
| 6 | 1005 | Islands | $9.00 |
West and Islands match none of the three tests and get the final value, $9.00. When every test compares the same cell with a fixed value, as here, SWITCH writes the same rule with each region once: =SWITCH(B2,"North",5,"South",7,"East",6,9). See the SWITCH page.
Nested IF with AND
A nested IF can combine its levels with AND or OR when one band depends on two cells. A rep with sales of 2,000 or more and at least 3 years gets 10%, anyone else over 2,000 gets 5%, and the rest get nothing:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Years | Rate |
| 2 | Ana | 2400 | 4 | 10% |
| 3 | Ben | 2100 | 1 | 5% |
| 4 | Chloe | 1500 | 6 | 0% |
| 5 | Dan | 3000 | 3 | 10% |
| 6 | Eve | 900 | 2 | 0% |
Ana and Dan qualify for 10%, Ben has the sales but not the years and gets 5%, and Chloe and Eve get 0%. The order matters here too: the stricter test comes first.
IFS: the same thing without nesting
In Excel 2019, Excel 2021 and Microsoft 365, IFS takes the tests and results as pairs, with no inner IF and one closing bracket. TRUE as the last test acts as "everything else":
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")
It reads the conditions in the same order and stops at the first TRUE one, so the order rule above still applies. The IFS page covers it, including the #N/A it returns when no test matches. In Excel 2016 and earlier IFS is not available, and a file using it shows #NAME? there.
Practice: a three-band commission
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Ana | $6,200 | |
| 3 | Ben | $2,400 | |
| 4 | Chloe | $600 |
Your turn: In C2, pay 10% of the sales in B2 when they are 5000 or more, 5% when they are 1000 or more, and 0 otherwise. The formula fills down to C4.
A lookup table instead of many IFs
When the bands are numbers and there are more than three or four of them, keep the thresholds in a small table and look them up. The table is sorted from the lowest threshold up, and approximate match (TRUE as the last argument) returns the row of the largest threshold that is not above the score:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 94 | A | 0 | F | |
| 3 | Ben | 81 | B | 70 | C | |
| 4 | Chloe | 70 | C | 80 | B | |
| 5 | Dan | 65 | F | 90 | A | |
| 6 | Eve | 88 | B |
The results match the nested IF at the top of the page. To move the B band to 85, change E4 to 85: no formula changes, and every grade updates. With XLOOKUP the same lookup is =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1), where -1 means "exact match or the next smaller value"; the table does not need to be sorted then.
Try it: the band table is ready, write the lookup.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 86 | 0 | F | ||
| 3 | 70 | C | ||||
| 4 | 80 | B | ||||
| 5 | 90 | A |
Your turn: In C2, return the grade for the score in B2 from the band table in E2:F5.
Frequently Asked Questions
How do you write multiple IF statements in Excel?
Put the next IF in the value_if_false argument of the previous one: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))). Excel tests the conditions from the first to the last and stops at the first one that is TRUE.
How many IF functions can you nest in Excel?
Up to 64 levels in Excel 2007 and later. Well before that limit, a formula becomes hard to read and to check; with more than three or four bands, a lookup table with approximate VLOOKUP or XLOOKUP is easier to maintain.
Why does my nested IF return the wrong result?
Usually because the conditions are in the wrong order. With >= tests, start with the highest threshold: if B2>=70 comes first, a score of 95 stops there and gets the 70 band's result.
What can I use instead of nested IF in Excel?
IFS in Excel 2019 and later (=IFS(B2>=90,"A",B2>=80,"B",TRUE,"F")), SWITCH when you compare one value with fixed values, and a lookup table with =VLOOKUP(B2,$E$2:$F$5,2,TRUE) for numeric bands.
