We help IT Professionals succeed at work.

# Excel 2007 formula to find price based on 2 fields

on
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
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

regards, barry
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Where is quantity found?

Commented:
``````=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)))))
``````

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!

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??

Commented:
What values do you have for cells A2, A3, etc?

Do you have ther the material codes 32, 21, etc?
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
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

Commented:
Perfect just what I needed thanks