nested if iserror formula error - please help!

[agnesz]

I'm attempting to do an nested IF formula with an If(ISERROR statement preceding it and I keep getting an error message... can someone please help? I've tried everything and now I'm completely confused. Thanks

=IF(iserror(if($A9="T",(VLOOKUP($B9,TEMPLATE!$A$2:$EC$1000,22,FALSE))),IF($ A9="c",(VLOOKUP($B9,TEMPLATE!$A$2:$EC$1000,22,FALSE)),IF($A9="v",($M9-VLOOKUP($B9,INDIRECT($A$4&"!$B$8:$AE$526"),5,FALSE)))),0,(if($A9="T",(VLOOK UP($B9,TEMPLATE!$A$2:$EC$1000,22,FALSE)))),IF($A9="c",(VLOOKUP($B9,TEMPLATE !$A$2:$EC$1000,22,FALSE)),IF($A9="v",($M9-VLOOKUP($B9,INDIRECT($A$4&"!$B$8:$AE$526"),5,FALSE)))))

[gwkenny]

Looks like you've gone beyond 7 nested levels which isn't healthy. Excel will only officially support 7 levels (2003 and before).

Because the spreadsheet is not supplied and we don't know what the values are in the cells referenced, it is extremely hard/impossible for someone to debug your formula.

You can do it yourself though. You can use the formula auditing toolbar, or you can do it manually - which is what I do.

In the formula bar, highlight parts of your formula and hit F9. I usually start at the back. In this case, I'd highlight, "VLOOKUP($B9,INDIRECT($A$4&"!$B$8:$AE$526"),5,FALSE)" and hit F9. See if that evaluates as valid or errors out. Keep on doing this till you find the part of your formula that errors out.

Good luck!

[grichey]

yeah the formula evaluation is perfect for sorting out your #n/a's

[Zack Barresse]

What is the difference if A9= "T" or "c"? The formulas are the same. You're only checking for one other value "v", which you're performing a slightly different Vlookup on (with one cell value minus the result). Why can you not split these formulas up into multiple columns? Is there an actual need to have these formulas nested?

[agnesz]

got it!
tx

[Zack Barresse]

If that solves it for you, don't forget you can mark your thread as Solved by going to Thread Tools | Mark Thread Solved | Perform Action.