Apache OpenOffice (AOO) Bugzilla – Issue 105246
lookup function doesn't work in 3.1 and does not work in 3.2.1 either
Last modified: 2013-01-29 21:52:22 UTC
Lookup function doenst work in Ver. 3.1 and 3.1.1 I have a spreadsheet created in since vers. 2.x as long as i use the version 3.0 it works and returns the requested data. The same files used in version 3.1^or 3.1.1 i get some empty cells with no data returned. going back to version 3.0 everything works fine
Please attach such a document and specify which cell shows the wrong result.
Created attachment 66191 [details] Masterfile
Created attachment 66192 [details] the file which is looked up for updating
File Test is the file which gets updated from the file Testbsch The Cells in Row G are partly empty Since 3.1 it doesnt work anymore.
I see the empty cells in Column G in DEV300 m64 and see the no empty cells in OOo2.4.3. I find no reason. The problem remains, if you delete the links in column B. If you replace the text LBW4BR with CZ27ZZ in cell B7 in Test.ods, then that value is found in cell G7. The other way round, writing LBW4BR into cell B5, gives an empty result in Cell G5. What is special on LBW4BR? I have played around with the settings in Tools > Options > Calc > Calculate, but it doesn't solve the problem.
I have tested the Issue 105246 with the new release 3.2RC5. problem is still unsolved. the same error like in 3.1 Furthermore there is another error. If the file which is looked up does not contain the code, it still returns a result. The result however is wrong. It is the last result from the row before.
I have tested the new version 3.2.1 same problem as before. lokup does not return anything in the cell. Only version 3.0 does what it is supposed to do
I see no empty cells in DEV300m84 after having updated the links.
*** Issue 115824 has been marked as a duplicate of this issue. ***
@ggoebel: please can you test the reported issue in the latest developer milestone (see: "http://download.openoffice.org/next"), is it still occurring for you?
It seems working fine for me on 3.3 RC7 (OOO330m17) under Win Vista. G column looks complete.
.
I have tested 3.4dev300m94build9547. With my testfile i had sent it works. Testing with the complete working file it's still not ok. To me it seems oo has problems with letters e.g WCH888. I sent you the complete files for testing. i have marked the lines in yellow where the problem occurs.
Created attachment 75205 [details] Masterfile to be updatedt
Created attachment 75206 [details] update info file
Created attachment 75207 [details] update file
For LOOKUP the search vector must be sorted, which it is not in both linked files. The vector is defined as A2:A110 and contains empty cells at the end, and additionally a "SPPakt" text in A46 of Depot_Sparka_26.11.10.ods in between the empty cells.
Closing.
sorry i can not accept your comments dec 1. both linked files are sorted as required. i have reduced the search vector to A$2:A$40 and the resultvector to E$2:E$40 so it should only search in that lines. However if in the depotSparka file the sppakt is in line 45 (but outside the search and result area)the result are empty cells. It does make sense to define an search area when the programm still looks for other lines. And in the Depotconsor file the last value for WCH888 is returning a wrong result. Should be 133,5 but is 1,32 I have found another error which is in rel. 3.0.1 already and in the newer releases as well. If the search vektor is not found e.g. A0RNHR in line 71 it returns a value . it should return an error like not found or N/A and not any value.
The range A2:A40 accessed as search vector is not sorted. In Depot_Consor data is sorted in A2:A29, in Depot_Sparka data is sorted in A2:A33, both followed by empty cells included in the search range. A binary search on such arrangement yields unusable values. So actually this is a request to ignore empty cells in the search vector.
The Files Depot.. which update akteuro are not always withe the same number of lines. e.g. when you add a line (buy some add. shares) or delete lines beacause you have sold the shares. That would always require changes in the formula of the master. And that is certainly not the way electr. data processing should be done. I could agree on some form of end delimitter to signal the final of the file.