=ИНДЕКС(Лист1!$F$2:$F$13;ПОИСКПОЗ(B2&C2&D2&E2&F2;Лист1!$A$2:$A$13&Лист1!$B$2:$B$13&Лист1!$C$2:$C$13&Лист1!$D$2:$D$13&Лист1!$E$2:$E$13;0))

=СУММПРОИЗВ((Лист1!$A$2:$A$13=B2)*(C2=Лист1!$B$2:$B$13)*(D2=Лист1!$C$2:$C$13)*(E2=Лист1!$D$2:$D$13)*(F2=Лист1!$E$2:$E$13)*(Лист1!$F$2:$F$13))

Sub Transition()
    Dim A, B, i&, j&, k&
    A = ThisWorkbook.Worksheets(1).Cells(1, 1).CurrentRegion
    B = ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion
    For j = 2 To UBound(B, 1)
        For i = 2 To UBound(A, 1)
            For k = 1 To 5
                If A(i, k) <> B(j, k + 1) Then Exit For
            Next k
            If k = 6 Then
                B(j, k + 1) = A(i, k)
                Exit For
            End If
        Next i
    Next j
    ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion.Value = B
End Sub

Option Explicit
Public Sub test()
Dim arr1()
Dim i As Integer, j%, k%, buffRaw%
Dim buffBool As Boolean
Dim buff2 As Byte
Const strL1 = "Лист1"
Const strL2 = "Лист2"

With Worksheets(strL1)
    arr1 = .Range("A2:F13").Value
End With
With Worksheets(strL2)
    For i = 2 To 4
        buff2 = 0
        For j = 1 To UBound(arr1)
            buffBool = True
            For k = 1 To 5
             buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
            Next k
            If buffBool Then
                buff2 = buff2 + 1
                buffRaw = j
            End If
        Next j
        Select Case buff2
          Case 0
            .Cells(i, 7).Value = "Not Find"
          Case 1
            .Cells(i, 7).Value = arr1(buffRaw, 6)

          Case Else
            .Cells(i, 7).Value = "doubles"

        End Select
    Next i
End With

buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)