Return multiple results with WorksheetFunction.VLookup

[Exceladd1ct]

Hi, i am trying to get multiple results with WorksheetFunction.VLookup. The way i see it, i could use a function as bellow, but i got stuck getting the address of the value found by vLookUp Function.
I know it is easier to use Range.Find Method but it has a small delay on my big data set while WorksheetFunction.VLookup gets the results instantly.

Function lookup(tofind, firstrow)

    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Sheets("Data")
    
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim myColumnIndex as Long
    Dim myLastRow As Long
    Dim myVLookupResult
    Dim allstr As String
    
    Dim myTableArray As Range
    
    myFirstColumn = 1
    myLastColumn = 3
    myColumnIndex = 3
    myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    With ws
        Set myTableArray = .Range(.Cells(firstrow, myFirstColumn), .Cells(myLastRow, myLastColumn))
    End With
    
    On Error Resume Next
        myVLookupResult = Application.WorksheetFunction.VLookup(tofind, myTableArray, myColumnIndex, False)
    On Error GoTo 0
    
    If myVLookupResult <> "" Then

        'This is where it gets difficult, this is more like pseudocode

        'Save the result in a delimited string to work with later
        allstr = allstr & "|" & myVLookupResult

        'Define the starting point for the next lookup'
        firstrow = myVLookupResult.Address

        'Recall function
        
        Call lookup(tofind, firstrow + 1)
    End If

End Function

 

[jasonb75]

Save the result in a delimited string to work with later

Noting the above, what do you intend to do with the string later?

If you just want a delimited string then I would look at evaluating a formula like

=TEXTJOIN("|",1,FILTER(ResultColumn,CriteriaColumn=tofind,"")

but with a large data set there are probably a number of better methods that you could use.