Issue 58286

Created attachment 31705 [details]
Example of wrong LOOKUP function result

Check the syntax in the help. IMO the index-list should be sorted.

Check the syntax in the help. IMO the index-list should be sorted.

As documented in the online help, also in OOo1.x, the search vector must be
sorted. If it isn't, results are arbitrary.

Closing.

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 OpenOffice.org 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
OOo 1.1.4 they ALL WORK CORRECTLY.
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

Created attachment 31755 [details]
(V)LOOKUP function(s) work(s) differently in OOo 1.1 and 2.0

Hey, LOOKUP functions' family does not work all. It is not a function but
enhanced randomizer!

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.

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
how: http://support.microsoft.com/kb/181212/en-us

Let us know if these changes don't fix your problems.

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
version.

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 !

Created attachment 33134 [details]
Simple example showing LOOKUP function not working in OOo 2.0

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

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.

OK, OK, OK.
Now, please, could you explain to me why in OpenOffice.org 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 OpenOffice.org since versions before the
1.0.0, i cannot realize that you don't want to consider a bug like this !

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.

->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

   =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)

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.

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.

*** Issue 63259 has been marked as a duplicate of this issue. ***

*** Issue 63901 has been marked as a duplicate of this issue. ***

*** Issue 65057 has been marked as a duplicate of this issue. ***

*** Issue 78256 has been marked as a duplicate of this issue. ***