[Libreoffice-bugs] [Bug 117016] Formula to Get value of last non-empty cell does not return expected result (lookup function)

[bugzilla-daemon at bugs.documentfoundation.org]

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

Eike Rathke <erack at redhat.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|UNCONFIRMED                 |NEW
           Hardware|x86-64 (AMD64)              |All
            Version|6.0.3.2 release             |Inherited From OOo
     Ever confirmed|0                           |1

--- Comment #3 from Eike Rathke <erack at redhat.com> ---
This is almost a duplicate of bug 116216 but with two additional quirks.

0. Use master / 6.1 alpha as a prerequisite for the following
1. Instead of
   =LOOKUP(2,1/(NOT(ISBLANK(A1:A$1))),A1:A$1)
   write
   =LOOKUP(2,1/(NOT(ISBLANK(A$1:A1))),A$1:A1)
   and pull/copy that down.
   It seems the wrong order in the references adds to some confusion,
   e.g. if
   =LOOKUP(2,1/(NOT(ISBLANK(E6:E$3))),E6:E$3)
   in G6 is replaced with
   =LOOKUP(2,1/(NOT(ISBLANK(E$3:E6))),E$3:E6)
   the expected result is returned.
   Having done so, the formulas in the range G3:G6 return the expected
   result.
2. The lookup-vector has to be sorted, and error values usually sort
   behind numeric and string values. Starting from G7 down the vector is
   not sorted anymore as it is {1,#DIV/0,#DIV/0,#DIV/0,1,...} which
   yields arbitrary results when queried with a binary search algorithm.
   2a) Excel seems to either completely ignore error values in the case
       of LOOKUP, at least in this constellation, or it ignores that the
       range is not strictly sorted.
       This seems to be nowhere specified. It just "happens" to be an
       implementation detail. In fact in OOXML ECMA-376-1:2016
       18.17.7.202 LOOKUP error values are not mentioned at all. (which
       they are also not for 18.17.7.343 VLOOKUP but appear to be
       significant there).

-- 
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/20180423/dc152deb/attachment.html>