Find then copy

[Guest]

I'm working off of two worksheets.
Worksheet 1 contains all the data.
Worksheets 2 has partial data.
I need to use the data (in this case a part number) in cell A1 on worksheet
2 and find the same part number on worksheet 1 and copy the data in the
adjacent three cells on worksheet 1 to the cells adjacent to the part number
worksheet 2.

worksheet 1
A B C D
A100 x y z
A200 x
A300 y
A400
A500 x z

worksheet 2
A B C D
A300 y
A400
A500 x z

 

[Bernard Liengme]

I will assume there are 25 rows of data on Sheet1; adjust my formula as
needed
In B1 of Sheet 2 enter =VLOOKUP($A1,Sheet1!$A$1:$D$25,COLUMN(),FALSE)
Copy across to column D and down as far as needed.
Unhappily, because Excel is numerically biased, empty cell on Sheet1 show as
0s on Sheet2
So we need to make it look complicated with
=IF(VLOOKUP($A1,Sheet1!$A$1:$D$25,COLUMN(),FALSE)>"",VLOOKUP($A1,Sheet1!$A$1:$D$25,COLUMN(),FALSE),"")
best wishes

 

[Guest]

Bernard,
I need to locate the same part number on worksheet 1 from worksheet 2 and
then copy the correspoding data in the adjacent cells to worksheet 2.
Thanks for your help.

 

[Bernard Liengme]

That is exactly what the VLOOKUP does, unless by copy you mean you want "x"
not a formula.
For that use Copy | Paste Special Values to convert the formulas to values

 

[MarketingGuy]

Vegs,

Have you tried using VLOOKUP? I think that would do the trick for you.

MG

 

[Guest]

Bernard,
I got it working with the 1st function (zeros for blank cells like you
mentioned) so I tried the 2nd function and it is giving me all blanks.

=VLOOKUP($A2,'Lead Free Master test.xls'!data,3,FALSE)

=IF(VLOOKUP($A2,'Lead Free Master
test.xls'!data,4,FALSE)>"",VLOOKUP($A2,'Lead Free Master
test.xls'!data,4,FALSE),"")

Just for the record the initial problem I had was the look up data was in
text not numeric and in the second column.

 

[Guest]

Bernard,
The sign needs to be less than <
Thanks for your help..

=IF(VLOOKUP($A2,'Lead Free Master
test.xls'!data,4,FALSE)<"",VLOOKUP($A2,'Lead Free Master
test.xls'!data,4,FALSE),"")