How to link data in 2 excel files and combine them into 1 using macro?

FILE 1
data: Column A, B and C

FILE 2
data: Column A, and D

Use FILE 1 as the primary file.

Final Result: should have A , B C and D together in 1 spreadsheet.

Problem : Look up function but seems like it doesn't work well. the data is jumble up. I still to check using True false function. cos what is avai in A may not be found in B. 


Pri file has more information ( in terms of number of names ) than file 2.
name      data1       data2      data3

and in file 2:
name      data1       data2      data3  data4 


Solution:

Sub finder()
Dim rng1 As Range
Dim rng2 As Range
Dim celle As Range
Dim llastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set rng1 = Range(Sheets("Pri file").[A2], Sheets("Pri file").[A65536].End(xlUp))
Set rng2 = Range(Sheets("file2").[A2], Sheets("file2").[A65536].End(xlUp))

For Each celle In rng1
    If (IsError(Application.VLookup(celle, rng2, 1, False))) Then
        celle.Offset(0, 17) = "Not found in file2"
        Else: celle.Offset(0, 17) = "Found in file2"
    End If
Next celle

For Each celle In rng2
    If (IsError(Application.VLookup(celle, rng1, 1, False))) Then
        celle.Offset(0, 4) = "Not found in Pri file"
        Else: celle.Offset(0, 4) = "Found in Pri file"
    End If
Next celle

llastrow = Sheets("Pri file").[A65536].End(xlUp).Row
Set rng2 = Range(Sheets("file2").[E2], Sheets("file2").[E65536].End(xlUp))
i = 1

For Each celle In rng2
    If celle = "Not found in Pri file" Then
        Sheets("Pri file").Cells(llastrow + i, 1) = celle.Offset(0, -4)
        Sheets("Pri file").Cells(llastrow + i, 2) = celle.Offset(0, -3)
        Sheets("Pri file").Cells(llastrow + i, 3) = celle.Offset(0, -1)
        Sheets("Pri file").Cells(llastrow + i, 4) = celle.Offset(0, -2)
        i = i + 1
    End If
Next celle

Application.ScreenUpdating = True

End Sub