match and insert from one workbook to another

[maijiuli]

Hello,

I have a project to match SSN's between 2 workbooks and insert columns from
one sheet to another.

Example:

File1
Column A = SSN, Col B = Salary, C = DOB

File2
Col A = SSN, B = Address, C = State, D = Zip

I need to take SSN from file2 and find it in file1. If found match then
copy and paste columns B-D of file2 to end of record on file1.

Is there a formula for this type thing.

 

[RagDyer]

A simple set of Vlookup formulas will do the job.

Assumptions:
Datalist on Sheet1 starts in Row2, from A2 to C(whatever).

Datalist on Sheet2 starts in Row2 and goes from A2 to D100.

On Sheet1, enter this formula in D2:

=IF(ISNA(MATCH($A2,Sheet2!$A$2:$A$100,0)),"",VLOOKUP($A2,Sheet2!$A$2:$D$100,COLUMNS($A1:B1),0))

Copy across to F2,
Then select D2:F2, and copy down as needed.

 

[JW]

In column D, row 2 of File 1, use a formula such as the one below:
=VLOOKUP($A2,File2!$A:$D,2,FALSE)
in column E:
=VLOOKUP($A2,File2!$A:$D,3,FALSE)
in column F:
=VLOOKUP($A2,File2!$A:$D,4,FALSE)

These formulas will use the value in A2 to look for in range A of
File 2. If a match is found, it will return the record from the
different columns matching the criteria.

You will notice that the only difference in the formulas is the 2, 3,
and 4. This is the Column Index number and tells the formula which
column from the lookup array (A) to return the value from.

 

[maijiuli]

Thanks RagDyer,

Your answer works like a charm. There was one thing I forgot to tell you,
which I can take care of, is that some SSN's have duplicates on File1 which I
guess your formula will take the first one found? Anyway, it doesn't matter
because I will do a quick find match and eliminate the duplicates before
running your formula.

Also, your formula specifies sheet 1 and sheet 2 so I put the two worksheets
in the same file but if I need to use your formula for two seperate
workbooks, can you tell me where in your formula to put the names of the
workbooks? File1 and File2.

Another minor thing is that this formula will not get row A (headers) but
again this is not the formula's fault because row A doesn't have a SSN. I
will just copy paste and do a quick eyeball test.

Thanks a lot,

 

[RagDyer]

A lazy and *accurate* way to insert the path is to let XL do it for you.

Open both workbooks.
Click in the D2 cell containing the formula.

In the *formula bar*, select the first:
Sheet2!$A$2

Navigate to the other file and click in A2 (or whatever cell the datalist
starts in).

You'll see that XL filled in all the info for you.

Now click in the second
Sheet2!$A$2
And do the same thing.

Hit <Enter>
Now click back into D2 and edit and /or revise your range locations and
sizes if necessary.

This formula is now ready to be copied across and then down as before.