OK. Here you go. Clear all the texts you have written on the sheet.

Code:
Sub kTest()
    
    Dim MyData      As Variant
    Dim MyTable     As Variant
    Dim i           As Long
    Dim r           As Long
    Dim rFound      As Variant
    
    With Worksheets(1)
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        MyData = .Range("a2:f" & r).Value2
    End With
    
    With Worksheets(2)
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        MyTable = .Range("a2:c" & r).Value2
        .Range("a2:a" & r).Name = "Type"
        .Range("b2:b" & r).Name = "Manu"

        .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)"
        
        For i = 1 To UBound(MyData, 1)
            'put the Type nad Manu in F2 and G2 respectively
            .Range("f2") = MyData(i, 2) 'type
            .Range("g2") = MyData(i, 4) 'manu
            'match row
            rFound = .Range("h2").Value2
            If Not IsError(rFound) Then
                'insert part # in the array
                MyData(i, 5) = MyTable(rFound, 3)
            End If
        Next
    End With
    'write back the array with parts
    Worksheets(1).Range("a2:f" & UBound(MyData, 1) + 1) = MyData
    
End Sub