[Libreoffice-bugs] [Bug 121090] Help of vlookup and hlookup: Sort order or Order are Approximate

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121090

--- Comment #4 from Mike Kaganski <mikekaganski at hotmail.com> ---
(In reply to gmolleda from comment #3)
> The fourth parameter tells the function whether the search should be
> approximate or not, not whether the data is sorted or not.

This is incorrect statement. I have told you what the parameter does exactly;
that is based on the OpenDocument v1.2 standard [1]; it reads:

> Semantics:
> 
> If RangeLookup is omitted or TRUE or not 0, the first column of DataSource is
> assumed to be sorted in ascending order, with smaller Numbers before larger
> ones, smaller Text values before larger ones (e.g., "A" before "B", and "B"
> before "BA"), and False before True. If the types are mixed, Numbers are sorted
> before Text, and Text before Logicals; evaluators without a separate Logical
> type may include a Logical as a Number. The lookup will try to match an entry of
> value Lookup. From a sequence of identical values <= Lookup the last entry is
> taken. If none is found the largest entry less than Lookup is taken as a match.
> If there is no data less than or equal to Lookup, the #N/A Error is returned. If
> Lookup is of type Text and the value found is of type Number, the #N/A Error is
> returned. If DataSource is not sorted, the result is undetermined and
> implementation-dependent. In most cases it will be arbitrary and just plain
> wrong due to binary search algorithms.
> 
> If RangeLookup is FALSE or 0, DataSource does not need to be sorted and an exact
> match is searched. Each value in the first column of DataSource is examined in
> order (starting at the top) until its value matches Lookup. If no value matches,
> the #N/A Error is returned.
> 
> Both methods, if there is a match, return the corresponding value in column
> Column, relative to the DataSource, where the leftmost column in DataSource is 1

So, it clearly tells that the semantics of the fourth parameter is whether to
use a "range lookup" algorithm, based on "sorted" state of array (DataSource).

[1]
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018436_715980110

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20181101/708d87e5/attachment.html>