VBA - Faster alternative to Vlookup?

[danmcdon]

I have a macro that loops thousands of times, and I believe it is really being slowed down by several Vlookups occuring in the code. I have several large arrays with data, and I need to pull data from the array in each loop using a different lookup value. Currently I am using worksheetfunction.vlookup, but if you suggest a faster way I will be eternally grateful!

i.e.

Dim ArrData as Variant

ArrData = range("data") 'range is approx 10,000 x 5

Result = Application.WorksheetFunction.VLookup(LookupValue, Range("Data"), 5, -1)

 

[wigi]

Hello

How many times do you execute that VLOOKUP function within your code?
Because 10,000 is not excessively high, and also you do not search for an exact match - which is way faster.