Have I exceeded nesting limit

[KeLee]

Excuse the gargantuan formula.

In Excel 2003 I am comparing product groupings and prices of groupings to
maximize profitabillity on bundle sizes for products.

I have test data that is FALSE, FALSE, TRUE for the nested IF's.

However, my result is adding the TRUE and FALSE values together for the
third IF statement, and appearing to me to ignore the comma delimiting where
the fasle statement begins.

=IF(AND($D$20=1,$D$22=1),$J3,IF($D$20=1,$J3+VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$23,Tables!$C$11:$C$16)+10,3)),Tables!$C$11:$K$16,9),IF($D$22=1,$J3+VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$21,Tables!$C$2:$C$7)+1,3)),Tables!$C$2:$K$7,9),$J3+VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$21,Tables!$C$2:$C$7)+1,3)),Tables!$C$2:$K$7,9))+VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$23,Tables!$C$11:$C$16)+10,3)),Tables!$C$11:$K$16,9)))

IS this due to more than 7 levels of nesting? I'm not sure where to reset
the count. I think I have only 7 in each statement if it is followed from the
first IF.

IF it helps, my test data has:
$D$20 = 2 (User input cell)
$D$21 = 5 (Chosen by formula reference to external cells based on $D$20)
$D$22 = 1 (User input cell)
$D$23 = 1 (Chosen by formula reference to external cell based on $D$22)

Therefore I expect it to resolve to the following:

IF($D$22=1,$J3+VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$21,Tables!$C$2:$C$7)+1,3)),Tables!$C$2:$K$7,9)

$J3 = 255
The resultant lookup value is 100

To give 355

But my result is adding in the result of the next lookup after the comma to
add on an extra unwanted 35 and an incorrect total of 390.

I have evaluated the formula and it calculates it correctly to
IF(TRUE,255+100,......)

But still goes on to add in the rest.

I realise this is probably far too specific and convoluted to realisticaally
expect anyone to wade through it, but I'd sure appreciate any help that's on
offer.

My thanks and appreciation if you even finished reading the entire post!

KeLee

 

[Bernie Deitrick]

For starters, I would change the parts of your formula like:

VLOOKUP(INDIRECT("Tables!"&ADDRESS(MATCH($D$23,Tables!$C$11:$C$16)+10,3)),Tables!$C$11:$K$16,9

to

VLOOKUP($D$23,Tables!$C$11:$K$16,9)

HTH,
Bernie
MS Excel MVP

 

[KeLee]

Thanks very much, this leaves me with
=IF(AND($D$20=1,$D$22=1),$J3,IF($D$20=1,$J3+VLOOKUP($D$23,Tables!$C$11:$K$16,9),IF($D$22=1,$J3+VLOOKUP($D$21,Tables!$C$2:$K$7,9),$J3+VLOOKUP($D$21,Tables!$C$2:$K$7,9)+VLOOKUP($D$23,Tables!$C$11:$K$16,9))))

Then i can see that one of my loookup ranges was the MATCH range and not the
VLOOKUP range, I have stored the INDIRECTCell addresses with separate
formulas elsewhere to use in another part of the calculation

Thanks again for letting me seort the wood from the trees
KeLee