In the code above, T1-T13represents 13 different logical tests, and R1-R13 represents 13 different results. You can see that each IF function requires it’s own set of parentheses.
This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests.
Testing more than one condition
If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, you can nest multiple IF statements together in one formula. You’ll often hear this referred to as “nested IFs”.
The idea of nesting comes from embedding or “nesting” one IF function inside another
In the example shown, we are using nested IF functions to size shoes based on size US. The logic for a size goes like this:
| Size US | CM |
| 3.5 | 22 |
| 4 | 22.5 |
| 4.5 | 23 |
| 5 | 23.5 |
| 5.5 | 24 |
| 6 | 24.5 |
| 6.5 | 25 |
| 7 | 25.5 |
| 7.5 | 26 |
| 8 | 26.5 |
| 8.5 | 27 |
| 9 | 27.5 |
| 9.5 | 28 |
| 10.5 | 29 |
To build up a nested IF formula that reflects this logic, we can start by testing to see if the size is below 4. If TRUE, we return 22. If FALSE, we move into the next IF function. This time, we test to see if the score is less than 4.5. If TRUE, we return 22.5. If FALSE, we move into yet another IF function. And so on.
Eventually, the formula we have in cell D5 looks like this:
=IF(C5<4,22,IF(C5<4.5,22.5,IF(C5<5,23,IF(C5<5.5,23.5,IF(C5<6,24,IF(C5<6.5,24.5,IF(C5<7,25,IF(C5<7.5,25.5,IF(C5<8,26,IF(C5<8.5,26.5,IF(C5<9,27,IF(C5<9.5,27.5,IF(C5<10.5,28,29)))))))))))))
You can see that it’s important in this case to move in one direction, either low to high, or high to low. This allows us to return a result whenever a test returns TRUE, because we know that the previous tests have returned FALSE.
Making nested IFs easier to read
By their nature, nested IF formulas can be hard to read. If this bothers you, you can add line breaks inside the formula to “line up” the tests and results. See the video link on this page to see how it’s done.
