Hi!

would you guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

I would use the basic table with sub-tables for the all the "added junk".

OR, perhaps you can build one table with all the "added junk" built-in.

Monster formulas are a real pain to "fix" 6 months after you've created
them. Right now it's probably fresh in your mind but 6 months from
now...........WTF!!!!!!!!!

Biff

"paul" wrote in message
...
=INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))
my simple lookup formula above turned in to this nightmare....would you
guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

=IF(ISERROR(INDEX(INDIRECT(R$93),MATCH($C8,INDIREC T(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))),"not
available",
IF($F8="",INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT( R$94),-1),MATCH($E8,INDIRECT(R$95),-1)),
IF(ISERROR(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE)), "not available",
IF(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)<1,INDEX(IND IRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISN A(HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),
INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))+(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)+IF(ISNA(H LOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)))))))

this is a formula to retrive products prices of different sizes (some of
which arent available in all sizes) with surcharges if modifications are
selected.Some modifications arent applicable to all products,some
modifications are straight dollar sucharge,some are a percentage of the
original price while others may have a price or percentage surcharge and a
surchage dependent on width or height.It seemed easy at first(response to
a
forum post)
--
paul
remove nospam for email addy!