Lookup thinks data isn't sorted
 

I import data from a different application into a .csv file, then save it as
an Excel workbook base.xls, using Excel 2000; that data is in correct sorted
order, column A runs from 00-00000 to 99-99999; there are about 230 items in
that series. Another workbook, calcu.xls uses the LOOKUP function to extract
data from the first workbook. Everything worked fine until we added 6 new
items in base.xls. Now those last six item Lookups in calcu.xls return a
blank space (not an error) instead of the actual item. When I use the Sort
menu to sort base.xls, the problem goes away, even though the actual sequence
of the data remains unchanged. If I change the formulas to VLOOKUP, they can
find the data in the original base.xls file. I'd like to avoid changing all
my LOOKUPs to VLOOKUPs, it would take quite awhile.

If you don't want to sort I think you will have to use vlookup or
match/index combo

--
Don Guillett
SalesAid Software

"Deb G" <Deb wrote in message
...
I import data from a different application into a .csv file, then save it
as
an Excel workbook base.xls, using Excel 2000; that data is in correct
sorted
order, column A runs from 00-00000 to 99-99999; there are about 230 items
in
that series. Another workbook, calcu.xls uses the LOOKUP function to
extract
data from the first workbook. Everything worked fine until we added 6 new
items in base.xls. Now those last six item Lookups in calcu.xls return a
blank space (not an error) instead of the actual item. When I use the
Sort
menu to sort base.xls, the problem goes away, even though the actual
sequence
of the data remains unchanged. If I change the formulas to VLOOKUP, they
can
find the data in the original base.xls file. I'd like to avoid changing
all
my LOOKUPs to VLOOKUPs, it would take quite awhile.

Deb G wrote:
I import data from a different application into a .csv file, then save it as
an Excel workbook base.xls, using Excel 2000; that data is in correct sorted
order, column A runs from 00-00000 to 99-99999; there are about 230 items in
that series. Another workbook, calcu.xls uses the LOOKUP function to extract
data from the first workbook. Everything worked fine until we added 6 new
items in base.xls. Now those last six item Lookups in calcu.xls return a
blank space (not an error) instead of the actual item. When I use the Sort
menu to sort base.xls, the problem goes away, even though the actual sequence
of the data remains unchanged. If I change the formulas to VLOOKUP, they can
find the data in the original base.xls file. I'd like to avoid changing all
my LOOKUPs to VLOOKUPs, it would take quite awhile.

When you invoke a lookup formula with LOOKUP, the "lookup table" must ne
set in ascending order on its match-range (that is, on its leftmost
column) and maintained in ascending order when new records added to it.
BTW, could you post the LOOKUP formula that you're using?

I think that somehow an underlying setting in Excel was interpreting the base
file as not being in ascending order. Curiously, the next day the problem
went away, and it hasn't come back. FYI, here was one of the Lookup formulas:

=LOOKUP($A5,'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus'!$A:$A,'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus'!$K:$K)

where $A5 is the item ID.

Thanks,
Deb

"Aladin Akyurek" wrote:

Deb G wrote:
I import data from a different application into a .csv file, <snip

When you invoke a lookup formula with LOOKUP, the "lookup table" must ne
set in ascending order on its match-range (that is, on its leftmost
column) and maintained in ascending order when new records added to it.
BTW, could you post the LOOKUP formula that you're using?

Deb G wrote:
I think that somehow an underlying setting in Excel was interpreting the base
file as not being in ascending order. Curiously, the next day the problem
went away, and it hasn't come back. FYI, here was one of the Lookup formulas:

=LOOKUP($A5,'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus'!$A:$A,'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus'!$K:$K)

where $A5 is the item ID.

Thanks,
Deb


If an item ID is not in ...!$A:$A, you'll fetch a wrong value, in case
the value in ...!$A:$A is lexically or otherwise smaller than A5 is not
admissible. The remedy would be:

=IF(LOOKUP($A5,x!$A:$A)=$A5,LOOKUP($A5,x!$A:$A,x!$ K:$K)

Substitute
'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus' for x,
which I used as an abbreviation.

"Aladin Akyurek" wrote...
....
If an item ID is not in ...!$A:$A, you'll fetch a wrong value, in case
the value in ...!$A:$A is lexically or otherwise smaller than A5 is not
admissible. The remedy would be:

=IF(LOOKUP($A5,x!$A:$A)=$A5,LOOKUP($A5,x!$A:$A,x! $K:$K)

Substitute
'M:\Finished_Goods_Kanbans\[PullItemStatus.xls]PullItemStatus' for x,
which I used as an abbreviation.

This won't necessarily help. The pathname to the left of the filename is the
key. It means the workbook isn't necessarily open. When it's not open, *ALL*
external references into it resolve to *ARRAYS*, **NOT** ranges. Arrays
can't span 65,536 rows, but ..!$A:$A and ..!$K:$K would do so. That this is
an intermitent problem for the OP is likely due to this other workbook being
open sometimes and closed other times.

If this workbook won't necessarily be open, don't use entire column
references. Use $A$1:$A$65535 and similarly for col K.