To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=58286
User dridgway changed the following:
What |Old value |New value
================================================================================
Status|UNCONFIRMED |RESOLVED
--------------------------------------------------------------------------------
Resolution| |INVALID
--------------------------------------------------------------------------------
------- Additional comments from [EMAIL PROTECTED] Fri Jan 13 10:46:12 -0800
2006 -------
->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.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
