limits on lookup function?
 

I have found that the lookup function does not work if it is sifting through
lots of records. Also it seems to work better if the data is sorted. Is
there any limit on the number of records for the lookup function? Does
sorting the data help? What exactly are the limits to the lookup function?
thank you!

limits on lookup function?
 

Suggest that you read the help index for LOOKUP and VLOOKUP, especially
about the sorting issue.

--
Don Guillett
SalesAid Software

"Knox" wrote in message
...
I have found that the lookup function does not work if it is sifting
through
lots of records. Also it seems to work better if the data is sorted. Is
there any limit on the number of records for the lookup function? Does
sorting the data help? What exactly are the limits to the lookup
function?
thank you!

limits on lookup function?
 

Hi

Lookup, Vlookup and Hlookup all work faster if the data is sorted.

Vlookup and Hlookup can work quite happily with unsorted data and large
amounts of data, provided that you set the 4th parameter to False or 0

=VLOOKUP(A1,$C:$E$,3,0)
would look the value found in cell A1 in the whole of column C (all
65,536 rows - in XL2003 or lower) and return the corresponding value
from column E even though the data is unsorted.

If you omit the 4th parameter then it is assumed to default to True or 1
and it would return value in column E, where the value in column C was
not greater than the value from cell A1


--
Regards

Roger Govier


"Knox" wrote in message
...
I have found that the lookup function does not work if it is sifting
through
lots of records. Also it seems to work better if the data is sorted.
Is
there any limit on the number of records for the lookup function?
Does
sorting the data help? What exactly are the limits to the lookup
function?
thank you!

limits on lookup function?
 

the lookup_vector must be sorted for lookup to work
from help
"The values in lookup_vector must be placed in ascending order: ...,-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct
value. Uppercase and lowercase text are equivalent."

and

"If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP
gives the #N/A error value"
"Knox" wrote:

I have found that the lookup function does not work if it is sifting through
lots of records. Also it seems to work better if the data is sorted. Is
there any limit on the number of records for the lookup function? Does
sorting the data help? What exactly are the limits to the lookup function?
thank you!