vlookup function applied to the table containing equal values

[AlexIV]

Hello Everyone,

I need to generate an invoice (new table) extracting data from tables 1&2 below. The input values are: Product #, Quantity and Destination.
The ouput parameters are: price per kg , discount per kg, shipping per kg, total price.
I'm trying to use vlookup function but the challenge is the product column contains the same products.

Table 1. Product pricing
Product #	SAP ID	Product	Ship Qty	Price	Discount	Ex-works price USD/kg
111	1000000	A1	<100kg	$100.00	$5.00	$95
111	1000000	A1	100 -500kg	$80.00	$5.00	$75
111	1000000	A1	>500 kg	$70.00	$5.00	$65
121	1000001	B2	<100kg	$56	$7.00	$49
121	1000001	B2	100 -500kg	$54	$7.00	$47
121	1000001	B2	>500 kg	$51	$7.00	$44
Table 2 Shipping Cost
City	Cost, $/kg
NYC	5
Toronto	3
Seatle	4
Invoice
Product# Quantity Destination Price Shipping Total price
121 50 NYC 56.36 5 3058

<tbody>
</tbody>

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H
1	Product #	SAP ID	Product	Ship Qty	Price	Discount	Ex-works price USD/kg
2	111	1000000	A1	<100kg	$100.00	$5.00	$95
3	111	1000000	A1	100 -500kg	$80.00	$5.00	$75
4	111	1000000	A1	>500 kg	$70.00	$5.00	$65
5
6
7	121	1000001	B2	<100kg	$56	$7.00	$49
8	121	1000001	B2	100 -500kg	$54	$7.00	$47
9	121	1000001	B2	>500 kg	$51	$7.00	$44
10
11
12	City	Cost, $/kg
13	NYC	5
14	Toronto	3
15	Seatle	4
16
17
18	Product#	Quantity	Destination	Price	Shipping	Totalprice
19	121	50	NYC	56	5	3050
Sheet13

Cell Formulas
Range	Formula
E19	=VLOOKUP(C19,$A$13:$B$15,2,0)
F19	=B19*(D19+E19)
D19	{=INDEX($E$2:$E$9,MATCH(LOOKUP(B19,{0,"<100kg";100,"100 -500kg";501,">500 kg"}),IF($A$2:$A$9=A19,$D$2:$D$9,0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlexIV]

This?

Excel 2010

	A	B	C	D	E	F	G	H
1	Product #	SAP ID	Product	Ship Qty	Price	Discount	Ex-works price USD/kg
2	111	1000000	A1	<100kg	$100.00	$5.00	$95
3	111	1000000	A1	100 -500kg	$80.00	$5.00	$75
4	111	1000000	A1	>500 kg	$70.00	$5.00	$65
5
6
7	121	1000001	B2	<100kg	$56	$7.00	$49
8	121	1000001	B2	100 -500kg	$54	$7.00	$47
9	121	1000001	B2	>500 kg	$51	$7.00	$44
10
11
12	City	Cost, $/kg
13	NYC	5
14	Toronto	3
15	Seatle	4
16
17
18	Product#	Quantity	Destination	Price	Shipping	Totalprice
19	121	50	NYC	56	5	3050

<tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula E19 =VLOOKUP(C19,$A$13:$B$15,2,0) F19 =B19*(D19+E19) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D19 {=INDEX($E$2:$E$9,MATCH(LOOKUP(B19,{0,"<100kg";100,"100 -500kg";501,">500 kg"}),IF($A$2:$A$9=A19,$D$2:$D$9,0),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Brillinat!!! Took me a while to understand. It works! Thank you vey much!