Vlookup value based on a range of quantities formula

[mgiesige]

I'm trying to figure out a formula to return a price per item for specific items based on quantities specific to each of them. I can have one table with everything or I can do three separate tables; whichever is best. I've attached a sample using the single table.
For Example: Each item's price is based on a range. Item B will be a total price of $100 for a quantity of 1 through 36. So, buying 2 pieces of Item B will cost you $50 each or buying 36 pieces of Item B will cost you $2.78 each. Either way the total price is $100. Now, moving to 36 pieces and up to 72 pieces, the total cost would be $125. At 73 pieces, then someone needs to take a closer look, so I'd want it to return the text, "Get a quote."
I'm not sure how to write a formula for all of that. Ideas?

Sample vlookup and range.xlsx
	A	B	C	D	E	F	G	H	I
1	Item	Quantity	Total Price				Item	Quantity	Price Each
2	A	57	$ 105.00				B	70
3	A	114	$ 140.00						1.79
4	A	115	Get a Quote						125/70=$1.79
5	B	36	$ 100.00
6	B	72	$ 125.00
7	B	73	Get a Quote
8	C	26	$ 95.00
9	C	52	$ 115.00
10	C	53	Get a Quote
11	D	34	$ 100.00
12	D	68	$ 120.00
13	D	69	Get a Quote
14
Sheet1

Cell Formulas
Range	Formula
I3	I3	=C6/H2

 

[dalvin647]

Try that . Change any 13s in formula to however many rows you need.

=IFERROR(INDEX(SORT(FILTER($C$2:$C$13,$A$2:$A$13=G2,),,-1),MATCH(H2,SORT(FILTER($B$2:$B$13,$A$2:$A$13=G2,),,-1),-1))/H2,"Get a Quote")

Thanks.

Book3
	A	B	C	D	E	F	G	H	I
1	Item	Quantity	Total Price				Item	Quantity	Price Each
2	A	57	105				B	70	$ 1.79
3	A	114	140
4	A	115	Get a Quote
5	B	36	100
6	B	72	125
7	B	73	Get a Quote
8	C	26	95
9	C	52	115
10	C	53	Get a Quote
11	D	34	100
12	D	68	120
13	D	69	Get a Quote
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IFERROR(INDEX(SORT(FILTER($C$2:$C$13,$A$2:$A$13=G2,),,-1),MATCH(H2,SORT(FILTER($B$2:$B$13,$A$2:$A$13=G2,),,-1),-1))/H2,"Get a Quote")

 

[mgiesige]

dalvin647, that is an incredible formula! I can follow it and it works in my real spreadsheet. Thanks. Now, I'll go learn about sorts and filters in the index/match function. Thank you!