Vlookup Function That Searches The Entire Workbook in Excel - UDF

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

---