Vlookup / index match

[Planogrammer]

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 -

UPC	Long Description <tbody> </tbody>	Size <tbody> </tbody>	UOM	Case Pack	Height	Width	Depth
1480000034 <tbody> </tbody>	MOTTS REG APPLE JUICE <tbody> </tbody>	64 <tbody> </tbody>	FL <tbody> </tbody>	8	9.65	4.65	4.6
1480000039	MOTTS APPLE JUICE <tbody> </tbody>	128	FL	4	11.76	5.55	5.55
1480000131	MOTT'S MEDLEY APPLE 100% <tbody> </tbody>	64	FL	8	9.65	4.65	4.6

<tbody>
</tbody>

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

UPC

ITEM

LONG DESCRIPTION

SIZE

UOM

CASEPACK

BRAND

CATEGORY

SUBTYPE

PACKAGE TYPE

ELIGIBLE

STATUS

HEIGHT

WIDTH

DEPTH

1153502556 <tbody> </tbody>

162019

WLD HRVST Orgnc Jce Apple <tbody> </tbody>

64

oz

8

Wild Harvest

SS Bottled

Apple

Bottle

Yes

UA

1153550042 <tbody> </tbody>

16200

WLD HRVST Orgnc Jce Lmnade <tbody> </tbody>

64

oz

8

Wild Harvest

SS Bottled

Lemonade

Bottle

Yes

UA

1480000032 <tbody> </tbody>

161098

MOTT Apple Juice Clear <tbody> </tbody>

32

oz

12

MOTTS

SS Bottled

Apple

Bottle

Yes

A

<tbody>
</tbody>

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

 

[AhoyNC]

See if this works.
Formula can just be copied down and across as needed.

Excel Workbook
	A	B	C	D	E	F	L	M	N	O
1	UPC	ITEM	LONG DESCRIPTION	SIZE	UOM	CASEPACK	STATUS	HEIGHT	WIDTH	DEPTH
2	1153502556	162019	WLD HRVST Orgnc Jce Apple	64	oz	8	UA	1.1	2.1	3.1
3	1153550042	16200	WLD HRVST Orgnc Jce Lmnade	64	oz	8	UA	4.1	5.1	6
4	1480000032	161098	MOTT Apple Juice Clear	32	oz	12	A	7.2	8.2	9.2
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H
1	UPC	Long Description	Size	UOM	Case Pack	Height	Width	Depth
2	1480000034	MOTTS REG APPLE JUICE	64	FL	8	9.65	4.65	4.6
3	1480000039	MOTTS APPLE JUICE	128	FL	4	11.76	5.55	5.55
4	1480000131	MOTT'S MEDLEY APPLE 100%	64	FL	8	9.65	4.65	4.6
5	1153502556	WLD HRVST Orgnc Jce Apple	64	oz	8	1.1	2.1	3.1
6	1153550042	WLD HRVST Orgnc Jce Lmnade	64	oz	8	4.1	5.1	6
7	1480000032	MOTT Apple Juice Clear	32	oz	12	7.2	8.2	9.2
Sheet1

 

[Planogrammer]

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.

 

[Planogrammer]

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.

 

[AhoyNC]

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.

 

[Planogrammer]

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.

 

[AhoyNC]

I haven't tested your formula, but you would need to lock the cells. Try:

=INDEX($W$2:$W$375,MATCH(A2,$R$2:$R$375, 0) )

 

[Planogrammer]

Forgot about locking! Thank you! it's working well now