Find then copy

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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
 
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.
 
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),"")
 
Back
Top