Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 6th 06, 04:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 1
Default Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP?


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


  #2   Report Post  
Old August 6th 06, 06:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 5,600
Default Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP?

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup in vlookup - taking the result as array name SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM
Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? SteveC Excel Worksheet Functions 6 May 26th 06 01:57 PM
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 03:47 AM
Array or Vlookup Robert[_16_] Excel Programming 5 February 16th 04 09:30 PM
Loading Excel Array from VB Array Faster ExcelMonkey[_3_] Excel Programming 3 January 22nd 04 03:49 AM


All times are GMT +1. The time now is 05:38 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017