How do I write a VBA function in Excel that allow me to lookup values (with XLOOKUP) on 60+ Worksheets

Question

I have an Excel workbook consisting of 60+ Worksheets. In the main worksheet, I want to use the XLOOKUP to find and return the value of what I'm looking up. The VLOOKUP function will not work in this case because the return value is in the first column.

I can get by with nested XLOOKUP functions, but I got lost when the number of functions increases. To visualize this, here is the nested XLOOKUP functions that I meant:

=XLOOKUP([@Forms],APP1!C:C,APP1!A:A,
XLOOKUP([@Forms]APP2!C:C,APP2!A:A,
XLOOKUP([@Forms],APP3!C:C,APP3!A:A,
XLOOKUP([@Forms],APP4!C:C,APP4!A:A))))

Please note, I use excel in German, so I tried to translate the function into English. We use semi colons instead of colons. If anything missing or seems wrong, I apologize beforehand.

As you can see from my excel function above, four XLOOKUP functions (for the APP worksheets) are nested. But I have 60 APP worksheets (APP1 - APP60). This can get confusing with all the syntax and parentheses.

So, I searched for some VBA for VLOOKUP that does the same trick and I made some adjustment to work with XLOOKUP. But it doesn't work.

For the time being, I don't have any optional look-ups in mind, I just put them there just for the sake of the XLOOKUP's complete syntax.

Here is the VBA code:

Function XLOOKUPWORKBOOK( _
lookup_value As Variant, _
lookup_array As Range, _
return_array As Range, _
Optional if_not_found As String, _
Optional match_mode As Integer, _
Optional search_mode As Integer _
)

Dim mySheet As Worksheet
Dim value_to_return

On Error Resume Next

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

With mySheet

Set lookup_array = .Range(lookup_array.Address)

'Run the actual xlookup function on the current sheet that is being looped though.
value_to_return = WorksheetFunction.XLookup(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

End With

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


Next mySheet

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

End Function

When I use the custom XLOOKUP function as created above, it returns "0" which is incorrect. And I have no clue where to fix it.

This is my working worksheet

This is an example worksheet (APP1)

This is an example worksheet (APP1)

I would appreciate your help. TIA

Answer 1

Try this out:

'Search for `lookup_value` across all worksheets in the parent workbook of 
'  the range `lookup_array` and return a match from `return_array` if found
Function XLOOKUPWORKBOOK(lookup_value As Variant, lookup_array As Range, _
                          return_array As Range, Optional if_not_found As String, _
                          Optional match_mode As Integer = 0, _
                          Optional search_mode As Integer = 1)

    Dim mySheet As Worksheet
    Dim value_to_return, wb As Workbook

    Set wb = lookup_array.Worksheet.Parent 'the workbook to use.  Not safe to use ActiveWorkbook.
    
    For Each mySheet In wb.Worksheets
        With mySheet
            Set lookup_array = .Range(lookup_array.Address)
            Set return_array = .Range(return_array.Address) '<<<<
            'you can't suppy "if not found" here, or it will short-circuit your loop...
            value_to_return = Application.XLookup(lookup_value, lookup_array, return_array, , match_mode, search_mode)
        End With
        If Not IsError(value_to_return) Then Exit For 'Exit the loop once have a value to return.
    Next mySheet
    
    If IsError(value_to_return) And Not IsEmpty(if_not_found) Then
        value_to_return = if_not_found 'safe to apply this here...
    End If

    XLOOKUPWORKBOOK = value_to_return

End Function

Answer 2

Thanks for your help, Tim! I tried it, but it didn't work. I still got "0" in all cells.

But it doesn't matter now because I just realized that Macros can't be run on the Excel web app. We usually use Excel in the web app on this client project. Therefore, running Macros is off the table.