How can we Vlookup Sheet1 data in sheet2 with partial match Columns C

Question

Looking VBA macro/Code which can do this simple match/partial match function in Excel spreadsheet.

I have 2 sheets in a excel workbook.

Sheet1 Contains
ColumnA = First Name
ColumnB = Last Name
ColumnC = Job Title

Sheet2 Contains
ColumnA = First Name
ColumnB = Last Name
ColumnC = Job Title
ColumnD = Emails

I want the macro to vlookup/match Sheet1 ColumnA, B, C with Sheet2 ColumnA, B, C and get Sheet2 ColumnD data into Sheet1 columnD with respective row matched.

Note:
Data could be case sensitive while doing vlookup/match/partial match.
Have to do partial match Sheet1 and Sheet2 "C" Columns with respective rows

Below are attached files sample and results should look after running the macro.

Sample and results files I been through these posts but did not find the answer.

how to get data in sheet2 from sheet1 in excel

How to copy data from sheet1 to sheet2 with a condition in Excel

merge data with partial match in r

Excel VBA - Search for value from sheet1 in sheet2 and update with adjacent value from sheet1

Answer 1

You could try a FOR loop to compare the values:

Sub CompleteData()

Dim lastrow1 As Long, lastrow2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

lastrow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For x = 2 To lastrow1 'change to 1 if you have no headers
    For y = 2 To lastrow2 'change to 1 if you have no headers
        If ws1.Cells(x, 1).Value = ws2.Cells(y, 1).Value And ws1.Cells(x, 2).Value = ws2.Cells(y, 2).Value And ws1.Cells(x, 3).Value = ws2.Cells(y, 3).Value Then
            ws1.Cells(x, 4).Value = ws2.Cells(y, 4).Value
            Exit For
        End If
    Next y
Next x

End Sub

Answer 2

you could use AutoFilter() and filter Sheet2 columns A to C with corresponding value of each Sheet1 row:

Option Explicit

Sub CompleteData()
    Dim myRng As Range, cell As Range

    With Worksheets("Sheet1")
        Set myRng = .Range("A2", .cells(.Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues)
    End With

    With Worksheets("Sheet2")
        With .Range("C1", .cells(.Rows.Count, 1).End(xlUp))
            For Each cell In myRng
                .AutoFilter Field:=1, Criteria1:=cell.Value
                .AutoFilter Field:=2, Criteria1:=cell.Offset(, 1).Value
                .AutoFilter Field:=3, Criteria1:=cell.Offset(, 2).Value
                If Application.WorksheetFunction.Subtotal(103, .cells) > 1 Then cell.Offset(, 3).Value = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).cells(1, 4).Value
                .Parent.AutoFilterMode = False
            Next
        End With
        .AutoFilterMode = False
    End With
End Sub