WorksheetFunction.VLookup return multiple results

sahal · Feb 2nd 2017

I have looked for over a day here in Ozgrid and cannot find an answer to what I am trying to do.
All I need is a vba code that Vlookups and return the multiple results,

Eg; the lookup value is in sheet1 A1, data is in sheet2 columns A1:B40000, match the values in sheet2 A1:A40000 and returns the values from Sheet2 column B1:B40000.

Note:Its possible to find upto 5000 matches in sheet2 A1:A40000.

I have tried several ways to do this, such as Array formula (VERY SLOW), UDF (SLOW), VBA-AutoFilter(SLOW).
Is there any way to do this quickly?

Can anyone help?
Thanks a lot in advance!

richadj4 · Feb 3rd 2017

Re: Vlookups, Vba, Multiple results

Variety of possibilities. If you are looking for speed I'd suggest either a find loop, or testing every single line (faster than you'd think). The REALLY important part is not to write each individual response back, write to a variant array, and write that back in 1 sweep.

something like this:

Code

Sub wut()
Dim rData As Range
Dim vData As Variant
Dim i As Long
Dim rfound As Range
Dim rfirst As Range
If Application.WorksheetFunction.CountIf(Sheet2.Columns(1), Sheet1.Cells(1, 1).Value) = 0 Then Exit Sub
Set rData = Sheet1.Cells(2, 1).Resize(Application.WorksheetFunction.CountIf(Sheet2.Columns(1), Sheet1.Cells(1, 1).Value), 2)
vData = rData.Value
Set rfirst = Sheet2.Cells(1, 1)
Set rfound = Sheet2.Columns(1).Find(Sheet1.Cells(1, 1).Value)
vData(1, 1) = rfound.Value
vData(1, 2) = rfound.Offset(0, 1).Value
i = 2
Set rfound = Sheet2.Columns(1).FindNext(rfound)
While Not rfound.Address = rfirst.Address
    vData(i, 1) = rfound.Value
    vData(i, 2) = rfound.Offset(0, 1).Value
    i = i + 1
Wend
rData.Value = vData
End Sub

Display More

sahal · Feb 3rd 2017

Re: Vlookups, Vba, Multiple results

Hey Richad
Thank you very much for your time adn effort, unfortunately, I am getting an error when I run the code. Runtime error 9, script out of range.

richadj4 · Feb 3rd 2017

Re: Vlookups, Vba, Multiple results

on what line? and can you upload a sample set?

WorksheetFunction.VLookup return multiple results

Participate now!

Share

Users Viewing This Thread