Option Explicit

Sub qwert()
    Dim r, i, lr, f, o, m1, m2, u, rz, slf: Set slf = CreateObject("Scripting.Dictionary")
    Dim slo: Set slo = CreateObject("Scripting.Dictionary")
    With Worksheets("1")
        lr = .UsedRange.Rows.Count
        m1 = .Cells(1, 1).Resize(lr, 2).Value
    End With
    With Worksheets("2")
        lr = .UsedRange.Rows.Count
        m2 = .Cells(1, 1).Resize(lr, 2).Value
    End With
    For r = 2 To UBound(m2)
        f = m2(r, 2)
        If Len(f) > 0 Then
            slf(f) = m2(r, 1)
        Else
            slo(m2(r, 1)) = r
        End If
    Next r
    ReDim rz(1 To UBound(m1) + UBound(m2), 1 To 3)
    rz(1, 1) = "имя"
    rz(1, 2) = "отчество"
    rz(1, 3) = "фамилия"
    For r = 2 To UBound(m1)
        rz(r, 1) = m1(r, 1)
        f = m1(r, 2)
        If slf.exists(f) Then
            rz(r, 2) = slf(f)
            slf.Remove f
        End If
        rz(r, 3) = m1(r, 2)
    Next r
    If slf.Count > 0 Then
        u = slf.keys
        For i = 0 To UBound(u)
            rz(r, 3) = u(i)
            rz(r, 2) = slf(u(i))
            r = r + 1
        Next i
    End If
    If slo.Count > 0 Then
        u = slo.keys
        For i = 0 To UBound(u)
            rz(r, 2) = u(i)
            r = r + 1
        Next i
    End If
    Worksheets.Add
    Cells(1, 1).Resize(UBound(rz), UBound(rz, 2)) = rz
    Cells(1, 1).Resize(UBound(rz), UBound(rz, 2)).Columns.AutoFit
End Sub

let
    a = Table.NestedJoin(Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], (x)=> x[фамилия] <> null), {"фамилия"}, Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content], (x)=> x[фамилия] <> null), {"фамилия"}, "q", JoinKind.FullOuter),
    b = Table.ExpandTableColumn(a, "q", {"отчество", "фамилия"}, {"отчество", "q"}),
    c = Table.CombineColumns(b, {"фамилия", "q"}, (x)=> x{0}??x{1}, "фамилия") & Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], (x)=> x[фамилия] = null) & Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content], (x)=> x[фамилия] = null)
in
    c