The following calculation is in column D9, this is stating to look at the
Customer account # entered into Column D3 by the user and perform the
calculations based on Column D8 which references our cost, in D9 I have the
formula stated as follows for materials mark up calculation:
=IFERROR(VLOOKUP(D3,'SPECIAL
RATES'!A$1:F$66,6,FALSE)*D8,ROUND(LOOKUP(D8,{0,31, 61,101,151,201,301,1001,10000},{1.8,1.4,1.2,1,0.9, 0.8,0.73,0.65})*D8+D8,2))
However, in some instances the €˜SPECIAL RATES sheet may have other markup
rates or discount percentages off of the standard markup rate which is
referenced in the
(LOOKUP(D8,{0,31,61,101,151,201,301,1001,10000},{1 .8,1.4,1.2,1,0.9,0.8,0.73,0.65})
portion of the formula. I have determined how to reference special rates that
may be noted simply as a 1.5 or 2.2 multiplier, however, I am having a hard
time trying to make exceptions for other criteria in my Vlookup formula,
For instance, if the €˜Special Rates sheet may contain a note in the markup
columns (F2 thru F66) that states, €œMark-up: Below $500 -25%; $501-1000 -20%;
1000 and up-15%€� or €œRegular mark-up less 10%) Can someone please help me
integrate that into my original formula?