WorksheetFunction.VLookup return multiple results

  • 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!

  • 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:

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!