How Do I Identify the cell with the right cost?

[derickj]

I'm not sure how to approach this. I need write a formula to identify the cost based on the type of print and the quantity. This is the way the data table is constructed.
Qtr >=1 and <=11 and type = Laser Engraving is $2.24. How can I write a formula to select the right cost?

Type of Print Qtry

Laser Engraving

10

Qty Cap	1	12	25	50	75	100	250	500	1000	2500	5000
	11	24	49	74	99	249	499	999	2499	4999
Embroidered	$8.16	$14.39	$13.20	$6.45	$5.58	$5.54	$5.10	$4.95	$4.89	$4.86	$4.84
Printed	$0.99	$7.99	$6.50	$5.96	$4.99	$3.99	$3.72	$3.35	$3.17	$2.99	$2.98
Laser Engraving	$2.24	$9.24	$7.75	$7.21	$6.24	$5.24	$4.97	$4.60	$4.42	$4.24	$4.23

 

[Peter_SSs]

Please update your profile to show version as requested above.

Since your quantity ranges form a continuous sequence you don't need to check both upper and lower limit each time. Therefore you could also use a shorter formula like this.

25 01 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	Qty Cap	1	12	25	50	75	100	250	500	1000	2500	5000
5		11	24	49	74	99	249	499	999	2499	4999	1000000
6	Embroidered	$8.16	$14.39	$13.20	$6.45	$5.58	$5.54	$5.10	$4.95	$4.89	$4.86	$4.84
7	Printed	$0.99	$7.99	$6.50	$5.96	$4.99	$3.99	$3.72	$3.35	$3.17	$2.99	$2.98
8	Laser Engraving	$2.24	$9.24	$7.75	$7.21	$6.24	$5.24	$4.97	$4.60	$4.42	$4.24	$4.23
9
10
11	Type of Print	Quantity	Price
12	Printed	150	$3.99
13	Laser Engraving	10	$2.24
14	Embroidered	15000	$4.84
15	Laser Engraving	500	$4.60
Price

Cell Formulas
Range	Formula
C12:C15	C12	=HLOOKUP(B12,B$4:L$8,MATCH(A12,A$4:A$8,0))