Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto fill data into a cell from a lookup table | Excel Discussion (Misc queries) | |||
lookup and replace data | Excel Worksheet Functions | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Lookup Access data in Excel | Excel Worksheet Functions | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |