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

"Bernie Deitrick" wrote:

> 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" <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
>
>
>