Lookup vs. HLookup/VLookup - Performance issues

I really do not know the answer to your question. The three functions are very similar in that they look at a list of cells and return a value from a specified column/row. The horsepower is in the comparison of each cell to the condition (something that is done many times during a call to the function), not the returning of the value (which is a single occurrence). So, the matching algorithm should be the thing that drives the computation time.

I ran a brief test using the same lookup table (search range E2:E1001, return range K2:K1001 for LOOKUP; Column range E3:K1001, offset 7 for VLOOKUP) for each function.

Values in the first column of the table were generated randomly with a fractional value dependent on the position of the cell added to prevent duplicate values. These were copied, then the values pasted to remove the formula, then the column was sorted ascending.

Return values in column K were generated by =ROW(), copied, then the Values Pasted to remove the formula.

A single search value was entered manually in B2, then a further 999 were generated in B3:B1001 by adding a random amount between 1 and 30 to the value in the cell above.

C2 contained =VLOOKUP(B2,E$2:K$1001,7) for the test of VLOOKUP
replaced by =LOOKUP(B2,E$2:E$1001,K$2:K$1001) for the test of LOOKUP

Note that omitting the fourth argument in VLOOKUP sets it to obtain a "close match" rather than an "exact match".

In both tests, the formula was filled down to C1001

D2 contained =C1001 to return the last returned value to the top of the page.

It's assumed that the RANDBETWEEN calculation and addition operation in column B consumed the same amount of time in both test versions.

Test times between entry of the initial search value and return of the last return value were consistent for each version of the test:

VLOOKUP: 9 seconds
LOOKUP: 22 seconds

Regards,
Barry

From my point of view, the main differences are:

(1) only HLOOKUP and VLOOKUP allow us to require an *_exact match_*

(2) only LOOKUP allow us to work
with a search-where column sitting on the left of the search-for one
or,
with a search-where row sitting higher than the search-for one.

Yvan KOENIG (VALLAURIS, France) jeudi 29 juillet 2010 18:24:06