<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><span class="vcard"><a class="email" href="mailto:erack@redhat.com" title="Eike Rathke <erack@redhat.com>"> <span class="fn">Eike Rathke</span></a>
</span> changed
<a class="bz_bug_link
bz_status_NEW "
title="NEW - Formula to Get value of last non-empty cell does not return expected result (lookup function)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=117016">bug 117016</a>
<br>
<table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>What</th>
<th>Removed</th>
<th>Added</th>
</tr>
<tr>
<td style="text-align:right;">Status</td>
<td>UNCONFIRMED
</td>
<td>NEW
</td>
</tr>
<tr>
<td style="text-align:right;">Hardware</td>
<td>x86-64 (AMD64)
</td>
<td>All
</td>
</tr>
<tr>
<td style="text-align:right;">Version</td>
<td>6.0.3.2 release
</td>
<td>Inherited From OOo
</td>
</tr>
<tr>
<td style="text-align:right;">Ever confirmed</td>
<td>
</td>
<td>1
</td>
</tr></table>
<p>
<div>
<b><a class="bz_bug_link
bz_status_NEW "
title="NEW - Formula to Get value of last non-empty cell does not return expected result (lookup function)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=117016#c3">Comment # 3</a>
on <a class="bz_bug_link
bz_status_NEW "
title="NEW - Formula to Get value of last non-empty cell does not return expected result (lookup function)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=117016">bug 117016</a>
from <span class="vcard"><a class="email" href="mailto:erack@redhat.com" title="Eike Rathke <erack@redhat.com>"> <span class="fn">Eike Rathke</span></a>
</span></b>
<pre>This is almost a duplicate of <a class="bz_bug_link
bz_status_RESOLVED bz_closed"
title="RESOLVED FIXED - LOOKUP on array doesn't handle sorted error values correctly"
href="show_bug.cgi?id=116216">bug 116216</a> 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).</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>