https://bugs.documentfoundation.org/show_bug.cgi?id=117016
Eike Rathke <er...@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 <er...@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.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs