Vlookup Function That Searches The Entire Workbook in Excel - UDF

Add to Favorites

With this VLOOKUPWORKBOOK function, you will have to power to more quickly and easily analyse your data in Excel. This gives you the power to use one simple Vlookup formula to simultaneously search through every single worksheet in Excel, or just a selected few worksheets.

The syntax of this function is eactly the same as for the regular Vlookup function, so there is not much to learn.

Where to install the macro:  Module

This special function, VLOOKUPWORKBOOK, is a custom function in Excel and it is called a UDF, or User Defined Function - it must be installed inside of a Module in order to work.

Sections:

Syntax

UDF Code

Syntax

=VLOOKUPWORKBOOK(lookup_value, table_array, col_index_num, [range_lookup], [sheets_to_exclude_1],[sheets_to_exclude_2],[sheets_to_exclude_3],[sheets_to_exclude_4],[sheets_to_exclude_5])
Argument Description
lookup_value Value to search for - this must be in the left-most row of the table_array.
table_array

The table through which you will search. The lookup_value must be in the left-most column of this table.

Note: for the VLOOKUPWORKBOOK function, you should never include worksheet references in this argument; include only a simple range and the function will search for that range on all worksheets, unless otherwise specified.

col_index_num Number of the column from which you will get the data; the left-most column of the table_array is column 1.
[range_lookup]

[Optional]

TRUE/FALSE

TRUE means to look for a match, but, if no match is found, it returns the next highest value.

FALSE means to only return a result for an exact match of the lookup_value.

[sheets_to_exclude_1] [Optional] Name of the first sheet to exclude from the search.
[sheets_to_exclude_2] [Optional] Name of the second sheet to exclude from the search.
[sheets_to_exclude_3] [Optional] Name of the third sheet to exclude from the search.
[sheets_to_exclude_4] [Optional] Name of the fourth sheet to exclude from the search.
[sheets_to_exclude_5] [Optional] Name of the fifth sheet to exclude from the search.

[] means the argument is optional.

UDF Code

To get the above function in Excel, you will have to open the VBA window and insert this macro into a Module. (Alt + F11 > Insert > Module > Paste Macro)

Function VLOOKUPWORKBOOK( _
    lookup_value As Variant, _
    table_array As Range, _
    col_index_num As Integer, _
    Optional range_lookup As Boolean, _
    Optional sheets_to_exclude_1 As String, _
    Optional sheets_to_exclude_2 As String, _
    Optional sheets_to_exclude_3 As String, _
    Optional sheets_to_exclude_4 As String, _
    Optional sheets_to_exclude_5 As String _
)


'------------------------------------------------'
'------------------------------------------------'
'               TeachExcel.com
'------------------------------------------------'
'------------------------------------------------'


Dim mySheet As Worksheet
Dim value_to_return
Dim sheets_to_exclude

On Error Resume Next

'Put sheets to exclude into an array
sheets_to_exclude = Array(sheets_to_exclude_1, sheets_to_exclude_2, sheets_to_exclude_3, sheets_to_exclude_4, sheets_to_exclude_5)

'Cycle through all sheets in the workbook
For Each mySheet In ActiveWorkbook.Worksheets

    'Check if the sheet needs to be excluded or not.
    If Not (UBound(Filter(sheets_to_exclude, mySheet.Name)) > -1) Then

        With mySheet

            Set table_array = .Range(table_array.Address)

            'Run the actual vlookup function on the current sheet that is being looped though.
            value_to_return = WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)

        End With

        'Exit the loop once have a value to return.
        If Not IsEmpty(value_to_return) Then
            Exit For
        End If

    End If

Next mySheet

'Send the result back to the cell that contains the function.
VLOOKUPWORKBOOK = value_to_return

End Function








How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Tutorial Details
Downloadable Files: Excel File
Excel Forum