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

=INDEX(B2:E10,MATCH(A12,A2

where A12 = 32 and B12 = 1500

see this article for a better description

regards, barry

Solved

Posted on 2012-08-14

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

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

7 Comments

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

=INDEX(B2:E10,MATCH(A12,A2

where A12 = 32 and B12 = 1500

see this article for a better description

regards, barry

```
=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!

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

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,

....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,

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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Automate double vlookup using VBA | 11 | 58 | |

How to add prompts to user defined function? | 6 | 26 | |

Excel - text to column problem | 2 | 16 | |

exporting issues in Access | 2 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!