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

 

[coulombevin]

Hi derickj,

Here's what I would do if you have Excel 365: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=VLOOKUP(A2,A6:L8,1+MATCH(1,(B2<=B5:L5)*(B2>=B4:L4),0),FALSE)

Here's a preview of the result:

Classeur1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type of Print	Quantity	Price	total
2	Printed	150	3,99	598,5
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	1E+06
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 $
Feuil1

Cell Formulas
Range	Formula
C2	C2	=VLOOKUP(A2,A6:L8,1+MATCH(1,(B2<=B5:L5)*(B2>=B4:L4),0),FALSE)
D2	D2	=C2*B2

If you have an older version of excel, we could look a way around if the function VLLOKUP or Match is not accessible.

Bests regards,

Vincent

 

[kvsrinivasamurthy]

Try

A2=Laser Engraving, B2=10
Data range is A8:L12. A8=Qty Cap

=SUMPRODUCT((($B$8:$L$8<=B2)*($B$9:$L$9>=B2)*($A$10:$A$12=A2))*$B$10:$L$12)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[derickj]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Office 365

 

[derickj]

Hi derickj,

Here's what I would do if you have Excel 365: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=VLOOKUP(A2,A6:L8,1+MATCH(1,(B2<=B5:L5)*(B2>=B4:L4),0),FALSE)

Here's a preview of the result:

Classeur1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type of Print	Quantity	Price	total
2	Printed	150	3,99	598,5
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	1E+06
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 $
Feuil1

Cell Formulas
Range	Formula
C2	C2	=VLOOKUP(A2,A6:L8,1+MATCH(1,(B2<=B5:L5)*(B2>=B4:L4),0),FALSE)
D2	D2	=C2*B2

If you have an older version of excel, we could look a way around if the function VLLOKUP or Match is not accessible.

Bests regards,

Vincent

Seems to work great. Thanks

 

[coulombevin]

Seems to work great. Thanks

Hi Derickj,

It's a pleasure, if thats all, could you write it as solutions.

Thanks,

Vincent

 

[Fluff]

Office 365

Thanks for that, please don't forget to update your profile, so that people know for the future.

 

[derickj]

Hi Derickj,

It's a pleasure, if thats all, could you write it as solutions.

Thanks,

Vincent

Not sure what you mean write it as solutions?

 

[coulombevin]

Not sure what you mean write it as solutions?

Hi derickj,

Mark as solution will help other user to find quickly what worked if they face the same problem as you. See more here Mark as Solution

Bests regards,

Vincent