Can't figure out the right formula for data sharing between worksheets

[Petribikes]

Hi.

I'm by no means an Excel expert. I've been working on this for 2 days, searching google and trying everything. What I have is a workbook with 2 sheets. I'd like to find a way to import data from the 2nd sheet to the first sheet where the info in one column matches on both sheets. It seems easy enough, but I can't make it work.

What I'm trying to do is fill in the SKU, Cost, & Retail columns from sheet 2 into sheet 1 where the Style column matches on both sheets.

I've attached the file here. My actual file is about 30,000 items but I've only attached around 50 or 100 so i can get the idea how to do this.

HELP for this NOOB please!

Thanks!

[Jonmo1]

On the Product Sheet
B2: =INDEX(Cost!A$2:A$127,MATCH($A2,Cost!$B$2:$B$127,0))
D2: =INDEX(Cost!C$2:C$127,MATCH($A2,Cost!$B$2:$B$127,0))
E2: =INDEX(Cost!D$2:D$127,MATCH($A2,Cost!$B$2:$B$127,0))

[TMS]

B2:

=INDEX(Cost!$A:$A,MATCH(Product!$A2,Cost!$B:$B,0))

D2:

=VLOOKUP($A2,Cost!$B:$D,2,FALSE)

E2:

=VLOOKUP($A2,Cost!$B:$D,3,FALSE)

Copy all formulae down.


Regards, TMS

[Petribikes]

WOW! That worked. I was able to apply that to my large file and PRESTO! Thanks so much. Saved me tons of time. Now I'll be posting my next issue soon...

[Jonmo1]

You're welcome.

FYI, since you're returning multiple values based on the same lookup value...
It would be wise to put the Match into it's own cell, then use that in the INDEX..

Example

In an available column, Say column Z
Z2: =MATCH($A2,Cost!$B$2:$B$127,0)

Then the formulas from my previous post are

B2: =INDEX(Cost!A$2:A$127,$Z2)
D2: =INDEX(Cost!C$2:C$127,$Z2)
E2: =INDEX(Cost!D$2:D$127,$Z2)

This will save alot of calculation time.