Home > Blog

Avoiding massive nested IF statements in Excel

IF statements are very powerful formula in Excel.  The take the format of =IF(logical statement, what to show if true, what to show if false).  It is possible to insert another IF statement into the middle of another one such as 

=IF(Test A, if test A is true show this, IF(test B, if test B is true show this, if test B is false show this))

Thus if the value fails the first logical statement Excel will move onto the second test.   This makes the IF statement even more powerful, however with complex tests or many nested IF statements you can end up creating a monster. This is especially painful when trying to find errors or even when you return to a workbook after some time and try to work out what on earth it is doing and how!

In recent versions of Excel there is a new formula which can help with this: IFS.

An IFS statement works from left to right applying tests until it finds a match and then stops, for instance:

=IFS(test A, show if test A true, Test B, show if test B is true) and so on.

This is both much simpler to right and to read.

There is only one issue and this is that if none of the tests are true it displays an error.  To avoid this simply enclose the expression in an IFERROR or IFNA such as:

=IFERROR(IFS(A, something, B, something),what to show if nothing is true).

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.