Vlookup / index match

P

Planogrammer

New Member
Joined
Feb 25, 2016
Messages
5
Hey All,
So what I'm trying to do is pull data from 1 table or sheet into another. I have tried VLOOKUP & INDEX MATCH, on both separate sheets & also with both tables on the same sheet. I am trying to get Height Width & Depth fields to pull from one table to the other, by matching the UPC. So that only matches will populate, as there are different amounts of data in each table. The data would not line up by just dropping it in manually.

This is the table I would like to pull data from -

[TABLE="width: 500"]
<tbody>[TR]
[TD]UPC[/TD]
[TD][TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl73, width: 213"]Long Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl73, width: 47"]Size[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]UOM[/TD]
[TD]Case Pack[/TD]
[TD]Height[/TD]
[TD]Width[/TD]
[TD]Depth[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl73, width: 84"]1480000034[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl73, width: 213"]MOTTS REG APPLE JUICE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl73, width: 47"]64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl73, width: 53"]FL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8[/TD]
[TD]9.65[/TD]
[TD]4.65[/TD]
[TD]4.6[/TD]
[/TR]
[TR]
[TD]1480000039[/TD]
[TD][TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl73, width: 213"]MOTTS APPLE JUICE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]128[/TD]
[TD]FL[/TD]
[TD]4[/TD]
[TD]11.76[/TD]
[TD]5.55[/TD]
[TD]5.55[/TD]
[/TR]
[TR]
[TD]1480000131[/TD]
[TD][TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl73, width: 213"]MOTT'S MEDLEY APPLE 100%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]64[/TD]
[TD]FL[/TD]
[TD]8[/TD]
[TD]9.65[/TD]
[TD]4.65[/TD]
[TD]4.6[/TD]
[/TR]
</tbody>[/TABLE]

& I would like to pull the Height Width & Depth fields into this table -

[TABLE="width: 500"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]ITEM[/TD]
[TD]LONG DESCRIPTION[/TD]
[TD]SIZE[/TD]
[TD]UOM[/TD]
[TD]CASEPACK[/TD]
[TD]BRAND[/TD]
[TD]CATEGORY[/TD]
[TD]SUBTYPE[/TD]
[TD]PACKAGE TYPE[/TD]
[TD]ELIGIBLE[/TD]
[TD]STATUS[/TD]
[TD]HEIGHT[/TD]
[TD]WIDTH[/TD]
[TD]DEPTH[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl72, width: 84, align: right"]1153502556[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]162019[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl72, width: 212"]WLD HRVST Orgnc Jce Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]64[/TD]
[TD]oz[/TD]
[TD]8[/TD]
[TD]Wild Harvest[/TD]
[TD]SS Bottled[/TD]
[TD]Apple[/TD]
[TD]Bottle[/TD]
[TD]Yes[/TD]
[TD]UA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl72, width: 84, align: right"]1153550042[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]16200[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl72, width: 212"]WLD HRVST Orgnc Jce Lmnade[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]64[/TD]
[TD]oz[/TD]
[TD]8[/TD]
[TD]Wild Harvest[/TD]
[TD]SS Bottled[/TD]
[TD]Lemonade[/TD]
[TD]Bottle[/TD]
[TD]Yes[/TD]
[TD]UA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl72, width: 84, align: right"]1480000032[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]161098[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl72, width: 212"]MOTT Apple Juice Clear[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]32[/TD]
[TD]oz[/TD]
[TD]12[/TD]
[TD]MOTTS[/TD]
[TD]SS Bottled[/TD]
[TD]Apple[/TD]
[TD]Bottle[/TD]
[TD]Yes[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

These are the top 4 lines(including headers) from both tables, so as you can see, the UPCs do not line up when sorted, due to variety in the tables. The 5th line in the second table matches the 1st line in the 1st table, just as an example. But they still do not line up after that.

So where i'm getting stuck is figuring out the proper formula to match the Height Width & Depth in a row by matching the UPC between the tables. I've tried VLOOKUP & INDEX MATCH without success so far. Any help would be much appreciated.

Thanks
 
Sort by date Sort by votes
See if this works.
Formula can just be copied down and across as needed.

Excel Workbook
ABCDEFLMNO
1UPCITEMLONG DESCRIPTIONSIZEUOMCASEPACKSTATUSHEIGHTWIDTHDEPTH
21153502556162019WLD HRVST Orgnc Jce Apple64oz8UA1.12.13.1
3115355004216200WLD HRVST Orgnc Jce Lmnade64oz8UA4.15.16
41480000032161098MOTT Apple Juice Clear32oz12A7.28.29.2
Sheet2



Excel Workbook
ABCDEFGH
1UPCLong DescriptionSizeUOMCase PackHeightWidthDepth
21480000034MOTTS REG APPLE JUICE64FL89.654.654.6
31480000039MOTTS APPLE JUICE128FL411.765.555.55
41480000131MOTT'S MEDLEY APPLE 100%64FL89.654.654.6
51153502556WLD HRVST Orgnc Jce Apple64oz81.12.13.1
61153550042WLD HRVST Orgnc Jce Lmnade64oz84.15.16
71480000032MOTT Apple Juice Clear32oz127.28.29.2
Sheet1
 
Upvote 0
Thanks for the reply. I still get N/A with that formula unfortunately.
I tried working with both tables in the same sheet also, to see if that would be easier, but that's not working either. With both tables in the same sheet, my values for table 1 are UPC in Column A, Height Width & Depth in M N O. & for Table 2, UPC is in Column R, & Height Width Depth in W X Y.
When they are in separate sheets, the columns are as you have them above.
 
Upvote 0
I figured out the issue. My Formula of " =INDEX(W2:W375,MATCH(A2,R2:R375, 0) ) " was right....however, when I copy down the line, it was changing the the INDEX return value. So instead of staying at W2:W375, it was changing to W3:W376 & so on.
 
Upvote 0
The NA means it does not find a match.
Could it be that the UPC code in one table is text and it's a number in the other table. Also, check to make sure there are no extra spaces in one of the tables.
 
Upvote 0
my only issue now is, when i copy the formula, i need the A cell in the MATCH string to change, but I need the INDEX for W2:W375 & the MATCH R2:R375 to stay the same. I'm not sure how to let A change while keeping the other values the same.
 
Upvote 0
I haven't tested your formula, but you would need to lock the cells. Try:

Code: 
=INDEX($W$2:$W$375,MATCH(A2,$R$2:$R$375, 0) )
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Pivot, Vlookup, Index and Match, Powerpivot? What is the best method???
Replies
0
Views
700
ibesmond
I
M
  • Question Question
Excel Formula Help - need excel to look up a text and sum all values that have that text in multiple rows and columns
Replies
2
Views
918
Marcelo Branco
M
D
  • Question Question
Index+Match/ Vlookup help
Replies
1
Views
709
Scott T
Scott T
A
  • Question Question
Help with thrid critera with index match and greater than
Replies
1
Views
694
Fluff
Fluff
C
  • Question Question
Index Match help needed
Replies
9
Views
633
63falcondude
63falcondude

Forum statistics

Threads
1,226,835
Messages
6,193,234
Members
453,781
Latest member
Buzby

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