Nested IF in Excel
A nested IF puts one IF inside another so a formula can choose between three or more results.
What Nested IF does
Put the second IF in the place where the first would show its false result. Excel runs the tests from the left and stops at the first one that is true, so the order of the tests matters. Test the highest threshold first.
Syntax
=IF(test1, result1, IF(test2, result2, result3))
- test1
- The first comparison. If true, the formula stops and returns result1.
- test2
- Only checked when test1 is false.
- result3
- What to show when neither test is true.
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 72 | B |
| 3 | Ben | 48 | C |
| 4 | Cy | 64 | B |
| 5 | Di | 91 | A |
| 6 | Eli | 55 | C |
A score of 80 or more is an A. Otherwise 60 or more is a B. Everything else is a C.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Apply commission tiers or tax bands.
HR
Turn scores into rating labels.
Sales & Ops
Group orders into small, medium and large.
Common mistakes
- Putting the tests in the wrong order. If you test >=60 first, an 85 stops there and never reaches the A test.
- Brackets that do not balance. You need one closing bracket for each IF.
- Nesting too deep. Beyond three or four levels, a lookup table is easier to read and change.
The Nested IF course
- 1Grade each row three waysFree practice belowPractise
- 2Commission tiersComing soon
- 3Tax bandsComing soon
- 4Order of tests mattersComing soon
- 5When to switch to IFSComing soon
Common questions
How many IFs can I nest?
Excel allows 64 levels, but more than three or four is hard to read. A lookup table is usually a better choice.
Is there a simpler way?
Newer versions of Excel have the IFS function, which lists the tests one after another without nesting.