Issue 58286 - LOOKUP function works dirty in OOo2.0
Summary: LOOKUP function works dirty in OOo2.0
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0
Hardware: PC Windows XP
: P3 Trivial with 5 votes (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Keywords: oooqa
: 63259 63901 65057 78256 (view as issue list)
Depends on:
Reported: 2005-11-22 09:28 UTC by cianoz
Modified: 2007-08-07 09:50 UTC (History)
2 users (show)

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

Example of wrong LOOKUP function result (9.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2005-11-22 09:30 UTC, cianoz
no flags Details
(V)LOOKUP function(s) work(s) differently in OOo 1.1 and 2.0 (40.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2005-11-24 08:30 UTC, cianoz
no flags Details
Simple example showing LOOKUP function not working in OOo 2.0 (21.40 KB, application/vnd.sun.xml.calc)
2006-01-12 08:21 UTC, cianoz
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description cianoz 2005-11-22 09:28:22 UTC
In some circumstances the LOOKUP function does not work as expected: it returns
   a "Error: value not avaible"; it works correctly in many situations but not
in others, and i have not been able to understand the exact factors that causes
the problem.

I attach a spreadsheet that shows the examples i'm referring to.

In OOo 1.1.4 all the examples work correctly without errors; you can verify it
just saving my attachment in the 1.1 format and opening it with
this version of OOo.

I searched in qa project for an issue like this, i found others issue related to
LOOKUP but i think they're not the same as this.
Comment 1 cianoz 2005-11-22 09:30:14 UTC
Created attachment 31705 [details]
Example of wrong LOOKUP function result
Comment 2 jolatt 2005-11-22 17:09:15 UTC
Check the syntax in the help. IMO the index-list should be sorted.
Comment 3 jolatt 2005-11-22 17:09:54 UTC
Check the syntax in the help. IMO the index-list should be sorted.
Comment 4 ooo 2005-11-22 19:17:47 UTC
As documented in the online help, also in OOo1.x, the search vector must be
sorted. If it isn't, results are arbitrary.
Comment 5 ooo 2005-11-22 19:18:11 UTC
Comment 6 cianoz 2005-11-24 08:27:36 UTC
OK, theorically you're right, but in my opinion there is something wrong in OOo
2.0. Look at the new spreadsheet that i upload.

In my organization i spent 2 years for having success in replacing Micro$oft
Office with and it has been very hard to make people accepting
it. Now i have about 30 users of OOo and i created several much complex and
important spreadsheets that hardly use the LOOKUP and VLOOKUP funtions and in
I recently i tried to swith to the new OOo 2.0 but i had tons of problems with
these functions, opening/converting these files with OOo 2.0 made these files
widely unusable, so i had to swich back to 1.1 ver. 
These problems are not my opinion, it's a reality.

So please, get a look at the following spreadsheet because it looks like OOo 1.1
and the 2.0 version works differently with LOOKUP and VLOOKUP.
Thank you
Comment 7 cianoz 2005-11-24 08:30:24 UTC
Created attachment 31755 [details]
(V)LOOKUP function(s) work(s) differently in OOo 1.1 and 2.0
Comment 8 drwicked 2005-12-19 15:06:47 UTC
Hey, LOOKUP functions' family does not work all. It is not a function but
enhanced randomizer!
Comment 9 cianoz 2005-12-20 07:06:00 UTC
I ask to change the priority of this issue from P3 to P2; it is an important
defect and following to the rules we have to follow for assigning priority to
issues this one has all the requisites to pass to P2.
Comment 10 dridgway 2005-12-21 09:02:00 UTC
Hi cianoz,

"Arbitrary" means the result can change between versions. LOOKUP is behaving
correctly, it requires sorted data. (So does Excel.) V/HLOOKUP do not require
sorted data, but the column order is fixed (the new spreadsheet has the columns
wrong for VLOOKUP). Also, the 4th parameter must be FALSE to tell the function
that the data is unsorted. 

If you really want LOOKUP flexibility with unsorted data, Microsoft tells you

Let us know if these changes don't fix your problems.
Comment 11 cianoz 2006-01-12 08:16:08 UTC
The LOOKUP and VLOOKUP functions absolutely do not work correctly in OOo 2.0.x
and it's not just a matter related to sorted blocks of data or not. There are
many dirty things that OOo 2.0 do with these functions, differently from the 1.1

Please check the SIMPLE example i attach
(example2_LOOKUP_function_in_OOo_1.0-2.0.sxc) to compare how the old and new ver
of OOo work on this. It's clearly visible that something is changed!

OOo 2.0 is causing many problems in my organization with the bad LOOKUP an
VLOOKUP functions handling. Please, it's necessary that you put some more
attention to this issue !
Comment 12 cianoz 2006-01-12 08:21:13 UTC
Created attachment 33134 [details]
Simple example showing LOOKUP function not working in OOo 2.0
Comment 13 cianoz 2006-01-12 08:24:28 UTC
Note: the "Simple example showing LOOKUP function not working in OOo 2.0" file i
uploaded is in the 1.1 format but it's not influential, you can open open it
with OOo 2.0 and save it in the new OpenDocument Format or just use it in the
old format, never changes
Comment 14 dridgway 2006-01-12 14:57:55 UTC
From the docs, "the search vector for the LOOKUP must be sorted, otherwise the
search will not return any usable results." The search vector in your example is
$D$2:$D$5, and these values are not sorted. It seems to me that this example is
also due to an unsorted search vector.
Comment 15 cianoz 2006-01-12 16:00:23 UTC
Now, please, could you explain to me why in 1.1.x the search
vector for the LOOKUP function does not need to be sorted and now in OOo 2.0 it
does ?

You can clearly see how different is the result in the example i submitted (look
at the image i put in the spreadsheet, that shows CLEARLY how OOo 1.1 gives
correct result with the SAME formulas) !

It looks like you want do not want to confirm that the 2.0 ver of OOo works
differently with the LOOKUP functions and causes BIG problems with it !

I have tens of important sheets in my company and now they are completely
unusable with the 2.0 version ? What shoud i do with them ? Do you suggest to
switch to Microsoft Office ? 

I am a user and a big promoter of since versions before the
1.0.0, i cannot realize that you don't want to consider a bug like this !
Comment 16 tommy_bo 2006-01-13 00:09:48 UTC
I agree with cianoz, I see the attachment id=31705 and with OOo 2.0 for linux
there is the same problem with "Lookup" functions; the only way to know this
real bug is try to change order of rows or change value and random give errors;
I try the same with OOo 1.1.4 and all works great.
Comment 17 dridgway 2006-01-13 18:46:12 UTC
->tommy_bo: Please read the docs and the previous comments.

->cianoz: I haven't checked, but according to er's comment, the vector is
required to be sorted in 1.1.x documentation as well. If provided with an
unsorted list, LOOKUP is allowed to return anything, including the result you
expect for one version, and a result you didn't expect for the next version; or
a result you expect one day, and a result you didn't expect the next day. This
is what "arbitrary" means. This is in the definition of LOOKUP, which was not
created by OO.

Note that there's an algorithmic reason for this definition. An entry in a
sorted list of length N can be found in O(log(N)) steps, whereas an unsorted
list requires O(N) steps. Perhaps we should just check if the list is sorted
first? That itself requires O(N) steps! That's why LOOKUP requires sorted data,
and H/VLOOKUP need to be told if their data is unsorted.

Switching to Microsoft won't help. As noted before, Excel also requires the list
to be sorted. Your options are to a) stay with 1.1.x, if you feel confident that
this version will always work the way you expected, or b) fix your spreadsheets,
which seem to be relying on undocumented behavior of 1.1.x. The MS kb article
above suggests a construction along the lines of


for simulating a LOOKUP on unsorted data. You might try that.

Since Calc LOOKUP is behaving as documented, I'm going to close this issue. If
you find an issue with VLOOKUP not behaving as documented, please open a new issue.
Comment 18 dridgway 2006-03-20 00:44:26 UTC
One more thought: this can be considered an incompatibility in an undocumented
behavior -- ie., what LOOKUP does when presented with unsorted data. I now think
this incompatibility ought to at least be documented, which I've written up as a
separate issue: see issue 63381.
Comment 19 dridgway 2006-03-23 06:33:30 UTC
*** Issue 63259 has been marked as a duplicate of this issue. ***
Comment 20 Regina Henschel 2006-04-02 13:15:04 UTC
*** Issue 63901 has been marked as a duplicate of this issue. ***
Comment 21 frank 2006-05-15 13:05:57 UTC
*** Issue 65057 has been marked as a duplicate of this issue. ***
Comment 22 frank 2007-08-07 09:50:16 UTC
*** Issue 78256 has been marked as a duplicate of this issue. ***