Find value within table within a range

[sjemth]

Hi All,

I hope someone can help me with the below:

I have a reference table like this where each cell within the table shows a specific price, depending on the item (a b c d e) and depending on how often that item is ordered (within a range)
If the amount of ordered is higher than 100, I take the

0-100 101-1000 1001-1500 1501-1700 1701- 1800 1801- 2000
a price #1 price #2 ...
b price #7 ....
c ....
d
e

Now I need to find a formula that would give me the exact price depending on the item and the amount ordered.
So ie: if item a is ordered 800 times I would get price #2.

I hope I have been clear.

Thank you and kind regards,
Sam

[Glenn Kennedy]

One way, using INDEX-MATCH-MATCH

=INDEX($B$2:$G$3,MATCH(B9,$A$2:$A$3,0),MATCH($B$10,B1:G1,1))

as shown in the file

[sjemth]

Thank you Glenn but there is one issue I have with it still.
In your example the result for my exercise should be 160 as I surpassed 101 so I should take the price in column "D"
So range 1-101 = 180, range 102-1001 = 160 etc.

Am I being clear?

[Glenn Kennedy]

In the sheet that I attached, the 160 CHF band starts (as I think you specified) at 1001 units. In cell B10, I had left it at 1000 units - which has a price of 180 CHF. Or am I wrong....

[sjemth]

Yes thank you, I viewed it differently but it is what i need.

Have a nice day!

[Glenn Kennedy]

Grand!!

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.