Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP?

[erikhs]

Hi,

Say you have a database of 20 000 or so entries, and you wish to lookup 1000-3000 values in this database. Would it be faster than the worksheet function VLOOKUP, if you loaded the parts of the database you needed into an array and then used a loop to get the values. something like:
For i = 1 to lastlookupvalue
Lookup(i) = Application.Vlookup(lookupvalue(i), array, x, 0),
Next i
and pasting it back to the worksheet. Do you have experience of which is faster if run from VBA, and the database is separate from the lookup-values.

[Peter T]

Although Worksheet functions are very fast and efficient when used in cell
formulas they are not nearly as fast used in VBA, just calling the function
before it does anything takes time. Sometimes recreating a VBA equivalent in
code will work faster.

But for searching values in an array by far the most important factor is
whether or not the array is sorted. If it is you don't need to loop through
it until you find your match. Find examples of "Binary search". In essence
compare the value in the middle of the array. If the lookup or search value
is less discard the top half and repeat with the remaining portion of the
array, and so on until you get an exact match (or closest). Finding a value
in an array of 20,000 would involve at most 16 steps.

If the array is not sorted the worksheet function might be faster, I don't
know. You say you want to do 1000 -3000 searches in the same array, it would
be worth experimenting dumping the entire array to cells and writing your
lookup formula in cell(s). Only way to find out is to test different methods
with realistic data type and volume, including the process of extracting the
data array and whatever it is you want to do with the results.

Regards,
Peter T

"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Say you have a database of 20 000 or so entries, and you wish to lookup
> 1000-3000 values in this database. Would it be faster than the worksheet
> function VLOOKUP, if you loaded the parts of the database you needed
> into an array and then used a loop to get the values. something like:
> For i = 1 to lastlookupvalue
> Lookup(i) = Application.Vlookup(lookupvalue(i), array, x, 0),
> Next i
> and pasting it back to the worksheet. Do you have experience of which
> is faster if run from VBA, and the database is separate from the
> lookup-values.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=568754
>