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]

the product column contains the same products.

Then how do you know which to choose? The SAP ID? (in your example it varies with each product)

 

[steve the fish]

You would need a product unit weight column in there somewhere.

 

[AlexIV]

Product #

 

[AlexIV]

Then how do you know which to choose? The SAP ID? (in your example it varies with each product)

The product#

 

[AlexIV]

You would need a product unit weight column in there somewhere.

The product weight (quantuty) is the second column in invoice

 

[steve the fish]

i dont get where the 3058 gets calculated

 

[AlexIV]

i dont get where the 3058 gets calculated

1.product #, quantity (weight) and destination are entered in the invoice table
2. price is extracted from Table 1 and Shipping rate is extracted from Table2
3. Total price is calculated = weight x( price + shipping rate)

 

[steve the fish]

Ok instead of <100 100-500 and > 500 can you put 0 100 500 in those cells? Ive pasted your tables into excel as they appear above then in cell D22:

=LOOKUP(2,1/(($A$3:$A$10=A22)*($D$3:$D$10<=B22)),$E$3:$E$10)

cell E22:

=VLOOKUP(C22,$A$14:$B$16,2,0)

and cell F22:

=SUM(D22:E22)*B22

Makes sure all your dollar amounts are true numbers not text.

 

[AlexIV]

Ok instead of <100 100-500 and > 500 can you put 0 100 500 in those cells? Ive pasted your tables into excel as they appear above then in cell D22:

=LOOKUP(2,1/(($A$3:$A$10=A22)*($D$3:$D$10<=B22)),$E$3:$E$10)

cell E22:

=VLOOKUP(C22,$A$14:$B$16,2,0)

and cell F22:

=SUM(D22:E22)*B22

Makes sure all your dollar amounts are true numbers not text.

It does not work. I think there is an error in the formula =LOOKUP(2,1/(($A$3:$A$10=A22)*($D$3:$D$10<=B22)),$E$3:$E$10)
Is it supposed to be =LOOKUP(A22,1/(