vlookup function applied to the table containing equal values

A

AlexIV

New Member
Joined
Jan 18, 2018
Messages
7
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]
 
Sort by date Sort by votes
This?


Excel 2010
ABCDEFGH
1Product #SAP IDProductShip QtyPriceDiscountEx-works price USD/kg
21111000000A1<100kg$100.00$5.00$95
31111000000A1100 -500kg$80.00$5.00$75
41111000000A1>500 kg$70.00$5.00$65
5
6
71211000001B2<100kg$56$7.00$49
81211000001B2100 -500kg$54$7.00$47
91211000001B2>500 kg$51$7.00$44
10
11
12CityCost, $/kg
13NYC5
14Toronto3
15Seatle4
16
17
18Product#QuantityDestinationPriceShippingTotalprice
1912150NYC5653050
Sheet13
Cell Formulas
RangeFormula
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.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
sheetspread said:
This?

Excel 2010
ABCDEFGH
Product #SAP IDProductShip QtyPriceDiscountEx-works price USD/kg
A1<100kg
A1100 -500kg
A1>500 kg
B2<100kg
B2100 -500kg
B2>500 kg
CityCost, $/kg
NYC
Toronto
Seatle
Product#QuantityDestinationPriceShippingTotalprice
NYC

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]

[TD="align: right"]$100.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$95[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]

[TD="align: right"]$80.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$75[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1000000[/TD]

[TD="align: right"]$70.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$65[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]

[TD="align: right"]$56[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$49[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]

[TD="align: right"]$54[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$47[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1000001[/TD]

[TD="align: right"]$51[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$44[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3050[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E19[/TH]
[TD="align: left"]=VLOOKUP(C19,$A$13:$B$15,2,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F19[/TH]
[TD="align: left"]=B19*(D19+E19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Brillinat!!! Took me a while to understand. It works! Thank you vey much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
J
  • Question Question
Product Costing - looking up values based on name and calculating cost given components
Replies
3
Views
2K
jellybeancounter
J
H
  • Question Question
VLOOKUP and IFERROR
Replies
9
Views
89K
MARK858
MARK858
Rachaelsqa
  • Question Question
extract numbers then sumproduct values against another column of data
2
Replies
12
Views
2K
Rachaelsqa
Rachaelsqa

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top