G
Guest
=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,INDIRECT(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(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISNA(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(HLOOKUP(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)
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,INDIRECT(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(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISNA(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(HLOOKUP(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)