=IFERROR(LOOKUP(2,1/SEARCH(TABLE!$B$10:$Y$10,W6),TABLE!$B$10:$Y$10),"Нет в таблице ")

=ИНДЕКС(TABLE!B11:Y12; ПОИСКПОЗ(PUMP!T1;TABLE!A11:A12;0); ПОИСКПОЗ(PUMP!W1;TABLE!B10:Y10;0))

Sub Del_SubStr()
    Dim sSubStr As String 'искомое слово или фраза(может быть указанием на ячейку)
    Dim lCol As Long 'номер столбца с просматриваемыми значениями
    Dim lLastRow As Long, li As Long
    Dim lMet As Long
    Dim arr
 
    sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "Запрос параметра", "")
    If sSubStr = "" Then lMet = 0 Else lMet = 1
    lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "Запрос параметра", 1))
    If lCol = 0 Then Exit Sub
 
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    arr = Cells(1, lCol).Resize(lLastRow).Value
    Application.ScreenUpdating = 0
    Dim rr As Range
    For li = 1 To lLastRow
        If -(InStr(arr(li, 1), sSubStr) > 0) = lMet Then
            If rr Is Nothing Then
                Set rr = Cells(li, 1)
            Else
                Set rr = Union(rr, Cells(li, 1))
            End If
        End If
    Next li
    If Not rr Is Nothing Then rr.EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Sub qwe()
jlastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Sheets.Count
ilastrow = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
If Sheets(i).Cells(1, 1).Value = "" Then ilastrow = 0
Name = Sheets(i).Name
For j = 1 To jlastrow
If InStr(1, Sheets(1).Cells(j, 2).Value, Name) <> 0 Then Sheets(1).Cells(j, 2).Copy Sheets(i).Cells(ilastrow + 1, 1): ilastrow = ilastrow + 1
Next j
Next i
End Sub

Sub Run()
Call qqq
 If Sheets("MOTOR").Range("A1").Text = "MOTOR" Then Call Sheet2.Motor
If Sheets("PUMP").Range("A1").Text = "PUMP" Then Call Sheet3.Pump
If Sheets("PROTECTOR").Range("A1").Text = "PROTECTOR" Then Call Sheet4.Protector
If Sheets("INTAKE").Range("A1").Text = "ITAKE" Then Call Sheet5.Intake
If Sheets("AGH, GS").Range("A1").Text = "AGH,GS" Then Call Sheet6.Aghgs
End Sub