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="width: 593"]
<tbody>[TR]
[TD="colspan: 3"]Table 1. Product pricing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product #[/TD]
[TD]SAP ID[/TD]
[TD]Product[/TD]
[TD] Ship Qty[/TD]
[TD]Price[/TD]
[TD]Discount[/TD]
[TD="colspan: 2"]Ex-works price USD/kg[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]
[TD]A1[/TD]
[TD]<100kg[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]
[TD]A1[/TD]
[TD]100 -500kg[/TD]
[TD="align: right"]$80.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]
[TD]A1[/TD]
[TD]>500 kg[/TD]
[TD="align: right"]$70.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]
[TD]B2[/TD]
[TD]<100kg[/TD]
[TD="align: right"]$56[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]
[TD]B2[/TD]
[TD]100 -500kg[/TD]
[TD="align: right"]$54[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]
[TD]B2[/TD]
[TD]>500 kg[/TD]
[TD="align: right"]$51[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Table 2 Shipping Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD="colspan: 2"] Cost, $/kg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NYC[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toronto[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Seatle[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Product# Quantity Destination Price Shipping Total price[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]121 50 NYC 56.36 5 3058[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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/(