=ИНДЕКС(Лист2!A:A;ПОИСКПОЗ(A:A;Лист2!C:C;0))

=ПСТР(B4;ПОИСК("0";B4);ДЛСТР(B4))

Sub GetArt()
    Dim dic As Object
    Set dic = GetDic(Sheets("Лист2"))
    If dic.Count > 0 Then
        FillFromDic Sheets("Лист1"), dic
    End If
End Sub

Private Sub FillFromDic(sh As Worksheet, dic As Object)
    Dim ar1 As Variant
    Dim ar2 As Variant
    Dim yy As Long
    Dim rn As Range
    With sh
        yy = .Cells(.Rows.Count, 1).End(xlUp).Row
        ar1 = .Range(.Cells(1, 1), .Cells(yy, 1))
        Set rn = .Range(.Cells(1, 2), .Cells(yy, 2))
    End With
    ar2 = rn
    For yy = 4 To UBound(ar1, 1)
        If dic.Exists(ar1(yy, 1)) Then
            ar2(yy, 1) = dic.Item(ar1(yy, 1))
        Else
            ar2(yy, 1) = Empty
        End If
    Next
    rn = ar2
End Sub

Private Function GetDic(sh As Worksheet) As Object
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    
    Dim arr As Variant
    Dim yy As Long
    With sh
        yy = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Range(.Cells(1, 1), .Cells(yy, 2))
    End With
    
    Dim fullstr As String
    Dim substr As String
    Dim ii As Long
    For yy = 4 To UBound(arr, 1)
        fullstr = arr(yy, 2)
        ii = InStrRev(fullstr, " ")
        If ii > 0 Then
            If ii < Len(fullstr) Then
                substr = Mid(fullstr, ii + 1, Len(fullstr))
                dic.Item(substr) = arr(yy, 1)
            End If
        End If
    Next
    Set GetDic = dic
End Function