Searching applicable price based on various quantity

**Umer** · 12-24-2009, 11:37 PM

I am having problem coming up with formula that can provide applicable price of a product based on various price break depending upon Min Qty. ordered. I have copied below an example depicting my issue. I don't know any formula to come up with applicable Price based on Qty. Ordered. e.g. in case of product A if Qty. ordered is 75,000 the applicable price would be $1.5 since this price is for Min Qty. 50,000 and less than 100,000 units where next price break $1.25 becomes applicable.

Product Min Qty Price Qty. Ordered Applicable Price
A 50,000.00 1.50 75000 1.5
A 100,000.00 1.25 75000 1.5
B 120,000.00 2.50 200000 2
B 150,000.00 2.00 200000 2

**teylyn** · 12-25-2009, 12:06 AM

Hi Umer,

welcome to the forum. However ...

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

I'll move this to Excel Worksheet Functions. For your next questions, please try to find the appropriate forum before you start a new thread.

cheers

**Palmetto** · 12-25-2009, 12:10 AM

I think you have posted in the wrong forum. This should be in the General or Worksheet Function Forum, however . . .

You need to create a properly structured look up table in order to match the Product type and find the nearest matching quantity that is less than or equal to the order quantity.

=IF(INDEX(C7:F8,MATCH(A2,B7:B8,0),MATCH(B2,C6:F6,1))=0,"n/a",INDEX(C7:F8,MATCH(A2,B7:B8,0),MATCH(B2,C6:F6,1)))

See attached.

Edit: OOPS! Should've hit my refresh button before posting.

**Umer** · 12-25-2009, 01:48 PM

Thanks Palmetto,

I am not too familiar with the website yet. Sorry for not assigning my posting proper forum..I am still learning to use this forum. I appreciate your quick response. I will give your solution a try....