V lookup

C

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
 
S

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

markup rates 1
vlook up 0
Return corresponding information with minimum value 2
Military Rate/Rank Formula 1
Multiple Criteria Lookup 3
drop down list selection 1
Lookup problem 2
Lookup table for rates? 4

Top