LinkBack URL
About LinkBacksFor 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" <KeLee@discussions.microsoft.com> wrote in message
news:FECDAD0D-1B3F-4AAE-A726-EB4115C047D6@microsoft.com...
> 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
Register To Reply
Bookmarks