Find value in third column based on values in two cells from two columns

Hi -

So I have been struggling with this for a good minute.

I want to find the maximum value in one column based on whether a cell value is within a range of another column's values which is based on another cell value in another column.

For example, I have a pricing list consisting of products, product codes, order quantities and pricing. I'm trying to find what the new price would be for a product code based on the quantities ordered for my analysis.

So, for example, if a customer ordered 525 quantities of TOP2505, this would result in the 500 Order Quantity Pricing range and would result in the pricing of $1.58. I've attached a workbook example.

However, the pricing list that I have does not have ranges built into it--it is only a list of order quantities--and there are multiple prices in the list. (I would like to pull the highest price value for that quantity range.)

I was able to come up with:


{=MAX(IF(B2:B18=G9,IF(C2:C18>=H9,D2:D18)))}


But this is bringing me the next price up (qty for 1,000); not the range between 500 and 1,000 (which should be 500).

What's more, is that this is an array; my actual workbook contains a year's worth of orders/quantities and I can't drag this formula down my list (and you know my list of products isn't limited to tops).

Does anyone know if there's a way to do this? Or is this just impossible?

This is brilliant, perfect!
Thank you both, so very much, for your help with this!