A well known limitation to Excel is that you cannot "nest" more than 7
functions. For example, the following formula will fail because the limit is
exceeded.

**=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,**

IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,

IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

As a general "rule of thumb," if you have a formula with more than 7 nested
statements, you should consider using a VBA function instead. However, if you do not
want to use VBA, you can get around this limitation by creating a defined name that refers
to part of the formula. Since defined formulas are evaluated separately, you can
have one or more defined formulas which refer to large formulas, and combine these into a
"master" formula.

Suppose we wanted an nested IF formula to test:

IF A4 = 1 Then 11

Else If A4 = 2 Then 22

Else If A4 = 3 Then 33

Else If A4 = 4 Then 44

...

Else If A4 = 13 Then 130 Else "Not Found"

and so on. Of course, as a practical matter, we'd be better off using a VLOOKUP to do this, but this will work for illustration.
First, we'd create a named formula called OneToSix,
referring to the formula:

**=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,**

IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,

IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

Then create another named formula called SevenToThirteen,
referring to the formula:

**=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,**

IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,

IF(Sheet1!$A$4=13,130,"NotFound")))))))

Finally, enter the "master" formula in the worksheet cell:

**=IF(OneToSix,OneToSix,SevenToThirteen)**

This "beats" the nested function limitation because no single part of the
formula exceeds the limit, even though the "sum" of the components do.

You can use this technique whenever you exceed the limit on nested functions.