Function IsBookOpen(wbFullName As String) As Boolean    Dim iFF As Integer
    iFF = FreeFile
    On Error Resume Next
    Open wbFullName For Random Access Read Write Lock Read Write As #iFF
    Close #iFF
    IsBookOpen = Err
End Function
Sub zhas()
Application.ScreenUpdating = False
Dim i As Long, f As Long, n As Long, wb As Workbook, awb As Workbook
sh = ActiveSheet.Name = "Лист1" ''лист, куда подставляем данные
Set wb = ActiveWorkbook
ash = "Книга2" 'имя листа, откуда надо взять данные в исходном файле
If IsBookOpen("C:\Users\Zh.Zholamanov\Desktop\zhastalap\RD report\zhastalap.xls") = True Then check = 1 Else check = 0
Set awb = GetObject("C:\Users\Zh.Zholamanov\Desktop\zhastalap\RD report\zhastalap.xls") ''полное имя файла
For i = 2 To 7
    For f = 2 To 5
       n = awb.Worksheets(ash).Rows(1).Find(what:=wb.Worksheets(sh).Cells(1, f).Value).Column
        wb.Worksheets(sh).Cells(i, f) = Application.VLookup(wb.Worksheets(sh).Cells(i, 1).Value, awb.Worksheets(ash).Range("A:E"), n, False)
    Next f
Next i
If check = 0 Then awb.Close 0
Application.ScreenUpdating = True
MsgBox "Конец!", vbInformation, "Конец"
End Sub

Sub zhas()
Dim i As Long, f As Long, s As String, n As Long, sh As Worksheet, skm As Workbooks
Set skm = Workbooks("plan.xlsm")
Set sh = Worksheets("ТРУ")
    For i = 2 To 7
        For f = 2 To 5
        n = sh.Rows(1).Find(what:=Cells(1, f).Value).Column
            Cells(i, f) = Application.VLookup(Cells(i, 1).Value, skm.sh.Range("A:G"), n, False)
        Next f
    Next i
 MsgBox "Конец!", vbInformation, "Конец"
End Sub

VBA Sub zhas()
Dim i As Long, f As Long, s As String, n As Long
        For i = 2 To 7
        n = 1
        For f = 2 To 5
            Cells(i, f) = Application.VLookup(Cells(i, 1).Value, Range("Книга2!A:E"), n + 1, False)
            n = n + 1
        Next f
    Next i
    MsgBox "Загрузка завершена!"
End Sub

=ЕСЛИ(C2=0;ЕСЛИ(D2="ТПХ";ПРОСМОТР(2;1/(ЛЕВСИМВ($A$1:$A$605;ПОИСК("-";$A$1:$A$605)-1)=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1));$C$1:$C$605)*B2/100;" 
");"") 

=ЕСЛИОШИБКА(СУММ(ИНДЕКС($C$1:$C$600;Ч(ИНДЕКС(ПОИСКПОЗ({1:2};СЧЁТЕСЛИ(СМЕЩ($A$1;;;СТРОКА($A$1:$A$600));ПСТР(A1;1;ПОИСК(" ";A1)-1)&"*");0))))*B1/100;"")

=ЕСЛИ(C2=0;ЕСЛИ(D2="ТПХ";ПРОСМОТР(2;1/(ЛЕВСИМВ($A$1:$A$605;ПОИСК("-";$A$1:$A$605)-1)=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1));$C$1:$C$605)*B2/100;" ");"")