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!