Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overcome LOOKUP limits and NOW() | Excel Worksheet Functions | |||
Function for upper and lower control limits? | Excel Worksheet Functions | |||
Limits of MINV matrix function | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
How do i set up if function with more than level limits? | Excel Worksheet Functions |