Issue 105246 - lookup function doesn't work in 3.1 and does not work in 3.2.1 either
Summary: lookup function doesn't work in 3.1 and does not work in 3.2.1 either
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 3.2.1
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
: 115824 (view as issue list)
Depends on:
Reported: 2009-09-21 17:33 UTC by ggoebel
Modified: 2013-01-29 21:52 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Masterfile (20.47 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-11-19 17:40 UTC, ggoebel
no flags Details
the file which is looked up for updating (244.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-11-19 17:41 UTC, ggoebel
no flags Details
Masterfile to be updatedt (234.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-12-01 15:29 UTC, ggoebel
no flags Details
update info file (18.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-12-01 15:30 UTC, ggoebel
no flags Details
update file (19.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-12-01 15:30 UTC, ggoebel
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description ggoebel 2009-09-21 17:33:52 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

going back to version 3.0 everything works fine
Comment 1 Regina Henschel 2009-09-21 22:18:25 UTC
Please attach such a document and specify which cell shows the wrong result.
Comment 2 ggoebel 2009-11-19 17:40:14 UTC
Created attachment 66191 [details]
Comment 3 ggoebel 2009-11-19 17:41:22 UTC
Created attachment 66192 [details]
the file which is looked up for updating
Comment 4 ggoebel 2009-11-19 17:45:50 UTC
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.
Comment 5 Regina Henschel 2009-11-19 19:18:30 UTC
I see the empty cells in Column G in DEV300 m64 and see the no empty cells in

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.
Comment 6 ggoebel 2010-02-08 16:21:27 UTC
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.
Comment 7 ggoebel 2010-07-19 13:13:33 UTC
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
Comment 8 ooo 2010-07-19 13:32:54 UTC
I see no empty cells in DEV300m84 after having updated the links.
Comment 9 Marcus 2010-11-29 15:08:32 UTC
*** Issue 115824 has been marked as a duplicate of this issue. ***
Comment 10 Marcus 2010-11-29 15:10:56 UTC
please can you test the reported issue in the latest developer milestone (see:
""), is it still occurring for you?
Comment 11 vitriol 2010-11-30 08:21:45 UTC
It seems working fine for me on 3.3 RC7 (OOO330m17) under Win Vista. G column
looks complete. 
Comment 12 Oliver Brinzing 2010-11-30 11:46:09 UTC
Comment 13 ggoebel 2010-12-01 15:27:17 UTC
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.
Comment 14 ggoebel 2010-12-01 15:29:17 UTC
Created attachment 75205 [details]
Masterfile to be updatedt
Comment 15 ggoebel 2010-12-01 15:30:02 UTC
Created attachment 75206 [details]
update info file
Comment 16 ggoebel 2010-12-01 15:30:45 UTC
Created attachment 75207 [details]
update file
Comment 17 ooo 2010-12-01 17:59:54 UTC
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.
Comment 18 ooo 2010-12-01 18:00:14 UTC
Comment 19 ggoebel 2010-12-02 22:57:34 UTC
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.
Comment 20 ooo 2010-12-03 12:03:34 UTC
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.
Comment 21 ggoebel 2010-12-03 16:22:12 UTC
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
I could agree on some form of end delimitter to signal the final of the file.