HI,

I have made some more comments and write the actual sheet names in the code. Hope this helps you.

Code:
Sub kTest()
    
    Dim MyData          As Variant
    Dim MyTable         As Variant
    Dim i               As Long
    Dim r               As Long
    Dim rFound          As Variant
    Dim wksIniDevices   As Worksheet
    Dim wksPartNumList  As Worksheet
    Dim rngIniDevices   As Range
    
    Set wksIniDevices = ThisWorkbook.Worksheets("Initiating devices")
    Set wksPartNumList = ThisWorkbook.Worksheets("Sheet2")
    
    With wksIniDevices
        r = .Range("a" & .Rows.Count).End(xlUp).Row         'find last row in col A
        Set rngIniDevices = .Range("a2:f" & r)              'range where your data
        MyData = rngIniDevices.Value2                       'convert the range into variant array as array works faster than range
    End With
    
    With wksPartNumList
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        MyTable = .Range("a2:c" & r).Value2                 'part num list
        .Range("a2:a" & r).Name = "Type"                    'define a name range to use in formula
        .Range("b2:b" & r).Name = "Manu"                    'define a name range to use in formula

        .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)" 'put the formula. see f2 and g2 - adjust these cells if you use a different cells
        
        For i = 1 To UBound(MyData, 1)
            'put the Type nad Manu in F2 and G2 respectively.adjust f2 and g2 if necessary
            .Range("f2") = MyData(i, 2) 'type
            .Range("g2") = MyData(i, 4) 'manu
            'find 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
    rngIniDevices = MyData
    
End Sub