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

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?

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") _
            ) _
        ) _
    )

<h2> An example using the For Each loop</h2>
For simplicity let's say you have just 2 sheets (Sheet1 and Sheet2) with the following data:

^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.

<hr>

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)