Vlookup across multiple workbooks

Here is one I just threw together.

Disclaimer: test everything on test files first! I combined some macros to save time so it doesn't look pretty but it should work.

Sub VlookupMultipleWorkbooks()

Dim xDirect, xFname
Dim Dest As Range
Dim Tbl_Array As Range

Application.ScreenUpdating = False


On Error Resume Next

'Change this to put the data in a different place in the workbook.
Set Dest = ActiveCell.Offset(0, 1)

Look_Value = ActiveCell.Value


With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a folder"
.Show
    If .SelectedItems.Count <> 0 Then
        xDirect = .SelectedItems(1) & "\"
        xFname = Dir(xDirect, 7)

        Do While xFname <> ""

            If (xFname Like "*test*") = True And Not (xFname Like ThisWorkbook.Name) Then

                Set wb = Workbooks.Open(xDirect & xFname)

                For Each wSheet In wb.Worksheets

                    With wSheet

                        vFound = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 2, False)

                        If Not vFound = vbNullString Then

                            Dest.Value = vFound

                            wb.Close SaveChanges:=False

                            Application.ScreenUpdating = True

                            Exit Sub

                        End If

                    End With

                Next wSheet

                wb.Close SaveChanges:=False

                xFname = Dir

            Else

                xFname = Dir

            End If

        Loop

    End If
End With

Application.ScreenUpdating = True

End Sub

The cell that is selected in the current workbook is what will be used as the lookup value.

"*test*" this part says which files to go through in the directory that you will select. In this case, each file with the word test in it will be searched.

Range("A1:B10") is the lookup range, just like for a regular vlookup. Change this range reference to whatever you need it to be. The way this macro works, is that the lookup range, this range here, must be in the same location for every workbook that you are searching.

The next value after the range, 2, is the column lookup value for the vlookup function. It works like it does for the regular vlookup function in Excel - 2 means to get values from the second column of the lookup range.

I think that that's all you have to change. When the macro runs, you select the directory that contains the files to search through.

Hardcoded Directory Path

Sub VlookupMultipleWorkbooks_HardCoded_Directory()

Dim fPath, xFname
Dim Dest As Range
Dim Tbl_Array As Range

Application.ScreenUpdating = False


On Error Resume Next

'Change this to put the data in a different place in the workbook.
Set Dest = ActiveCell.Offset(0, 1)

Look_Value = ActiveCell.Value


fPath = "C:\Your Directory Path\"
xFname = Dir(fPath)

Do While xFname <> ""

    If (xFname Like "*test*") = True And Not (xFname Like ThisWorkbook.Name) Then

        Set wb = Workbooks.Open(fPath & xFname)

        For Each wSheet In wb.Worksheets

            With wSheet

                vFound = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 2, False)

                If Not vFound = vbNullString Then

                    Dest.Value = vFound

                    wb.Close SaveChanges:=False

                    Application.ScreenUpdating = True

                    Exit Sub

                End If

            End With

        Next wSheet

        wb.Close SaveChanges:=False

        xFname = Dir

    Else

        xFname = Dir

    End If

Loop



Application.ScreenUpdating = True

End Sub