Sub bb()
Dim p$, c As Range
p = ActiveWorkbook.Path & "\["
For Each c In Columns(1).SpecialCells(xlCellTypeConstants)
    With c.Offset(, 3).Resize(, 6)
        .FormulaArray = "=VLOOKUP(RC[-1],'" & p & c & ".xls]" & c.Offset(, 1) & "'!R1C1:R9999C7,{2,3,4,5,6,7},)"
        .Value = .Value
    End With
Next
End Sub

Sub bb1()
Dim p$, c As Range
p = ActiveWorkbook.Path & "\["
For Each c In Columns(1).SpecialCells(xlCellTypeConstants)
    With c.Offset(, 3).Resize(, 6)
        .FormulaArray = "=INDEX('" & p & c & ".xls]" & c.Offset(, 1) & "'!R1C2:R999C7,MATCH(RC[-1],'" & p & c & ".xls]" & c.Offset(, 1) & "'!R1C1:R999C1,),)"
        .Value = .Value
    End With
Next
End Sub

{ =ИНДЕКС('C:\temp\1\[Книга1.xls]Лист1'!$B$1:$G$999;ПОИСКПОЗ(C3;'C:\temp\1\[Книга1.xls]Лист1'!$A$1:$A$999;);) }

"C" & c.Row