Sub FindArticul()
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim List1 As Worksheet
Set List1 = ThisWorkbook.Worksheets("Лист1")
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B3:C" & iLastRow).ClearContents
With List1
For i = 3 To iLastRow
Set cell = .Columns(1).Find(Cells(i, "A"), , xlValues, xlWhole)
If Not cell Is Nothing Then
Cells(i, "B") = cell.Offset(, 1) 'наименование
Cells(i, "C") = cell.Offset(, 2) 'ячейка хранения
Else
MsgBox "На листе1 нет артикула: " & Cells(i, "A")
End If
Next
End With
End Sub