VBA code for search identical data o cell and copy data from rows

[mihaibantas]

Hello everybody.
I'm calling you because I'm not very good at VBA code.
I have a problem that consists of the following:
I have Sheet1 with column A that contains some data. In Sheet2 Column A you will find some data that corresponds to Sheet1 Column A.
What I want to do is make the VBA code look up the same and identical data in Sheet2 Column A in Sheet1, then copy the data in Column B and Column C in front of Column A Sheet1 and where it finds duplicates, copy them with the specific data for that row.
I also attach images from the Excel file with the one I want.

Thank you in advance for taking the time to resolve my issue.

 

[igold]

Does this do what you want...

Sub test()

    Dim arr, arrF, arr2
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim wsF As Worksheet: Set wsF = Worksheets("Final")
    Dim lRow As Long, x As Long, n As Long
    
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range("A1:A" & lRow)
    Worksheets("Final").Range("C1").Resize(UBound(arr)) = arr
    arrF = wsF.Range("A1:C" & lRow)
    arr2 = ws2.Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    For x = 1 To UBound(arr)
        For n = 1 To UBound(arr2)
            If arr(x, 1) = arr2(n, 1) Then
                arrF(x, 2) = arr2(n, 3)
                arrF(x, 1) = arr2(n, 2)
            End If
        Next
    Next
    wsF.Range("A1").Resize(UBound(arrF, 1), UBound(arrF, 2)) = arrF
    
End Sub

 

[mihaibantas]

Hello igold, your code works great....thousands of thanks. I tried a variant with the formula "=LOOKUP(2,1/SEARCH($F$1:$F$13,C1),$G$1:$G$13)" but unfortunately it didn't work more than 1000 cells.
Thanks for your time.

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback.