Array VLOOKUPs take 6 times longer than worksheet range VLOOKUPs
 

I can post the code but I am running compartive performance tests.

I read 10k rows that split to output 22k rows. During this I do 100k
VLOOKUPS. Posting output directly to worksheet took 12 seconds. Creating an
array and then moving all 22k records to a worksheet range took 10 second
(20% reduction in processing time).

So far so good.

I moved the lookup table off the worksheet into an array (60 rows * 8
columns).

Processing time INCREASED to 60 seconds (600% increase!)

I must be doing something wrong but before I go nuts tweaking the code I
would like to know if this surprises you or, are some functions (like
VLOOKUPS) inherently slower when used in arrays?

I didn't name the array as a table (couldn't figure out how to do that but
the VLOOKUPs functioned by referencing "Array_Dates" without fail - just slow.

Any comments? I can post the code but thought the general concept would be
okay.

Thanks,
KG

Array VLOOKUPs take 6 times longer than worksheet range VLOOKUPs
 

Post the code, or the relevant bits of it.


"KGOldWolf" wrote:

I can post the code but I am running compartive performance tests.

I read 10k rows that split to output 22k rows. During this I do 100k
VLOOKUPS. Posting output directly to worksheet took 12 seconds. Creating an
array and then moving all 22k records to a worksheet range took 10 second
(20% reduction in processing time).

So far so good.

I moved the lookup table off the worksheet into an array (60 rows * 8
columns).

Processing time INCREASED to 60 seconds (600% increase!)

I must be doing something wrong but before I go nuts tweaking the code I
would like to know if this surprises you or, are some functions (like
VLOOKUPS) inherently slower when used in arrays?

I didn't name the array as a table (couldn't figure out how to do that but
the VLOOKUPs functioned by referencing "Array_Dates" without fail - just slow.

Any comments? I can post the code but thought the general concept would be
okay.

Thanks,
KG

Array VLOOKUPs take 6 times longer than worksheet range VLOOKU
 

Sam,

When the "Array_Dates" was a table on the spreadsheet, the procedure took 10
seconds, switching to and array caused a huge increase. This must be
happening because I have not named "Array_Dates" as a table - I think the
lookup is somehow examining every cell in the array until it finds a hit (it
will always find a hit).

I know there must be a way to name and set an array as a table - I just
haven't found it yet.

Thanks,
Ken




Print_Line_Array(Output_Row_Number, 8) =
Application.WorksheetFunction.VLookup(Lookup_Date, Array_Dates, 2, True)
' Days In Month


"Sam Wilson" wrote:

Post the code, or the relevant bits of it.