• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Excel 2007 formula to find price based on 2 fields

Hello EE,

I have the following
Material Code              QTY700          QTY2000         QTY4000           QTY8000
32                                       .40                  .33                  .20                     .19
21                                
What I am looking for is if there is a formula that would say if the Material code is x and the quantity y then the price will be z.  Thus if material is 32 and quantity is 1500 which is over 700 but under 2000 the price would be .40
0
bergquistcompany
Asked:
bergquistcompany
1 Solution
 
barry houdiniCommented:
It's simpler if the headers are just values, 700, 2000 etc.

If they are in B1:E1 and the Material codes are in A2:A10 then try this formula

=INDEX(B2:E10,MATCH(A12,A2:A10,0),MATCH(B12,B1:E1))

where A12 = 32 and B12 = 1500

see this article for a better description

regards, barry
0
 
Martin LissRetired ProgrammerCommented:
Where is quantity found?
0
 
DustinKikuchiCommented:
=IF(qty>8000,VLOOKUP(materialCode,A1:A??,5,TRUE),IF(qty>4000,VLOOKUP(materialCode,A1:A??,4,TRUE),IF(qty>2000,VLOOKUP(materialCode,A1:A??,3,TRUE),IF(qty>700,VLOOKUP(materialCode,A1:A??,2,TRUE)))))

Open in new window


Threw this together quickly but I believe it should do what you're asking.  It does only returns the unit price but could, of course, be easily modified to return a total line price.  It also doesn't return a value for quantities less than 700.
You may wish to convert your column headers into just the numeric quantities which would allow you to reference them should the quantity required for price break points need to be changed.

Leaving answer here for reference however the index answer above is certainly a better way to go about it.  I hadn't realized that the match function would work properly given the ranges.  I love learning new tricks!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bergquistcompanyAuthor Commented:
@barryhoudini I get #N/A and also if I put the example into Excel and try it I get the same error so I'm wondering if there is something I'm missing

Quantity is found in B1 is 700 C1 is 2000

@Dustin I am not getting a value on this.  Am I supposed to enter a value for the A??
0
 
joaoalmeidaCommented:
What values do you have for cells A2, A3, etc?

Do you have ther the material codes 32, 21, etc?
0
 
barry houdiniCommented:
I attached an example with some added random values...

I used the same formula I suggested above except I added some $ signs to the ranges so that they don't change when the formula is copied down a column, so in the attached example the formula in C12 is this

=INDEX(B$2:E$10,MATCH(A12,A$2:A$10,0),MATCH(B12,B$1:E$1))

....copied down to C14

That gives you the unit price for the material and quantity shown. In the next column I just multiplied the unit price by the quantity to get the Total Price. Of course you can do that all in one formula if you want like this:

=INDEX(B$2:E$10,MATCH(A12,A$2:A$10,0),MATCH(B12,B$1:E$1))*B12

As Dustin says, if quantity is less than 700 you'll get an error, is that possible? If so you'll need to enter another column, show zero in the header and below that the prices for 0-699

regards, barry
Material-costs.xls
0
 
bergquistcompanyAuthor Commented:
Perfect just what I needed thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now