How can I use Application.XLookup across every sheet in my workbook?

Question

Here is my code, first off:

Sub namelookup()
   SalesForm.BHSDTAPNAMELF.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("NICK TAPS").Range("S:S"), Worksheets("NICK TAPS").Range("T:T"), Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("CB TAPS").Range("S:S"), Worksheets("CB TAPS").Range("T:T"), Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("GG TAPS").Range("S:S"), Worksheets("GG TAPS").Range("T:T"))))
End Sub

This works for what I need, but how can I adjust this to not only look through specified sheets, but every single one on a workbook?

DecimalTurn · Accepted Answer · 2023-06-13T19:32:48.667

I see that you are using the "if_not_found" argument to nest multiple XLOOKUPs inside one another. That is indeed a way to go, but with more that 2 sheets, it's getting hard to read. You'll be better off looking at loops like in the example below.

Also as an aside, when you have a complex formula in the VB Editor, you can always split it on multiple rows for better readability:


    SalesForm.BHSDTAPNAMELF.Value = _
    Application.XLookup( _
        Val(SalesForm.BHSDMAINNUMBERLF.Value), _
        Worksheets("NICK TAPS").Range("S:S"), _
        Worksheets("NICK TAPS").Range("T:T"), _
        Application.XLookup( _
            Val(SalesForm.BHSDMAINNUMBERLF.Value), _
            Worksheets("CB TAPS").Range("S:S"), _
            Worksheets("CB TAPS").Range("T:T"), _
            Application.XLookup( _
                Val(SalesForm.BHSDMAINNUMBERLF.Value), _
                Worksheets("GG TAPS").Range("S:S"), _
                Worksheets("GG TAPS").Range("T:T") _
            ) _
        ) _
    )

An example using the `For Each` loop

For simplicity let's say you have just 2 sheets (Sheet1 and Sheet2) with the following data:

^data

To get the value from any sheets in the workbook, you could create a function with a loop over each sheet like this:


Public Function LookupAcrossSheets(LookupValue As Variant, FirstLookupArray As Range, FirstReturnArray As Range, MyWorkbook As Workbook) As Variant

    Dim temp As Variant
    
    Dim ws As Worksheet
    For Each ws In MyWorkbook.Sheets
        temp = Application.XLookup(LookupValue, ws.Range(FirstLookupArray.Address), ws.Range(FirstReturnArray.Address))
        If Not IsError(temp) Then
            Exit For
        End If
    Next
        
    LookupAcrossSheets = temp

End Function

Then you could use it in a sub like this:


Sub GetValue()

    Dim wb As Workbook
    Set wb = ThisWorkbook 'Or replace the reference here to the workbook you need
    
    MsgBox "Value for ""a"" is " & LookupAcrossSheets("a", wb.Sheets(1).Range("A:A"), wb.Sheets(1).Range("B:B"), wb)
    MsgBox "Value for ""e"" is " & LookupAcrossSheets("e", wb.Sheets(1).Range("A:A"), wb.Sheets(1).Range("B:B"), wb)

End Sub

Note that this approach relies on the fact that all the sheets are setup exactly the same way, so you can give the address for the first sheet and it will be reuse for the others.

EDIT: In your case, that would look like:


    Dim wb As Workbook
    Set wb = ThisWorkbook 'Or replace the reference here to the workbook you need
    
    SalesForm.BHSDTAPNAMELF.Value = LookupAccrossSheets(Val(SalesForm.BHSDMAINNUMBERLF.Value), wb.Sheets(1).Range("S:S"), wb.Sheets(1).Range("T:T"), wb)

wow this looks like exactly what I need. say im going to do multiple copies of the sub in a module, do i put the function at the top of the module? — cheese10234, Jun 12 '23 at 02:45
also, thank you for the tip on readability. I'm very new to VBA so everything I create is very ugly and this will help a lot. — cheese10234, Jun 12 '23 at 02:48
@cheese10234 - No, by default the function is Public, so it can be accessed from any modules, so you only need it once. — DecimalTurn, Jun 12 '23 at 02:48
...and the function can also be accessed from anywhere inside the module, so no need to put it at the top. — DecimalTurn, Jun 12 '23 at 02:57
You are a lifesaver. This was giving me such a hard time. If I could hug you I would. Lol. — cheese10234, Jun 12 '23 at 02:58
Can you tell me where I plug my original formula into this? As in ` SalesForm.BHSDTAPNAMELF.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("NICK TAPS").Range("S:S"), Worksheets("NICK TAPS").Range("T:T")` — cheese10234, Jun 13 '23 at 19:24
@cheese10234 - Check the edit for my answer to your previous comment. — DecimalTurn, Jun 13 '23 at 19:34
I figured it out right as you answered, but thank you! I should have tried harder before asking lol. — cheese10234, Jun 13 '23 at 19:45

How can I use Application.XLookup across every sheet in my workbook?

1 Answers1

An example using the `For Each` loop