Hi,

Say you have a database of 20 000 or so entries, and you wish to looku
1000-3000 values in this database. Would it be faster than the workshee
function VLOOKUP, if you loaded the parts of the database you neede
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 whic
is faster if run from VBA, and the database is separate from th
lookup-values

--
erikh
-----------------------------------------------------------------------
erikhs's Profile: http://www.excelforum.com/member.php...fo&userid=3278
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

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" wrote in
message ...

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