V lookup

[CandiC]

Below is a formula that I have set up for a multiplier in my "Cost Estimator"
worksheet In column D8 to look up the cost entered in D7 and mark up to the
sellable rate according to this schedule.
=ROUND(LOOKUP(D7,{0,31,61,101,151,201,301,1001,10000},{1.8,1.4,1.2,1,0.9,0.8,0.73,0.65})*D7+D7,2)

I have this formula repeated in columns D12, D16, D20 and D24 to allow the
entry of multiple costs on the same sheet, into D11,D15,D19, D23.

However, there are some instances where there are customers who require a
different markup rate or a discount on this rate. In the same workbook, I
have another spreadsheet set up called "special rates" this sheet identifies
special customer rates or discounts on the current rates by "customer account
number" identified in column A of that "special rates" sheet. I also have a
spot in the "Cost Estimator" in column D3 that the user can enter the
customer account number into. Is there a way that when the customer account
number is entere into the "Cost Estimator" sheet the markup rates can change
and pull from column G in the "special rates sheet"? My goal is mark up each
item used and have the total calculated at the end. Would this be an If,
then ...vlookup formula?

Please advise

 

[Sean Timmons]

First, purely from an aesthetics standpoint, I'd prefer to have the below as
a VLOOKUP. Merely enter your first set of values in column A of a sheet and
the second in column B, then

=ROUND(VLOOKUP(D7,Sheet1!A:B,2)*D7+D7,2)

Which would then lead to:

=IF(ISERROR(VLOOKUP(D3,'Cost
Estimator'!A:G,7,0)),ROUND(VLOOKUP(D7,Sheet1!A:B,2)*D7+D7,2),ROUND(VLOOKUP(D7,Sheet1!A:B,2)*VLOOKUP(D3,'Cost
Estimator'!A:G,7,0)+VLOOKUP(D3,'Cost Estimator'!A:G,7,0),2)