Bug 100266 - LOOKUP does not work the same as in Microsoft Excell
Summary: LOOKUP does not work the same as in Microsoft Excell
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-06-07 22:27 UTC by john@jbeidl.com
Modified: 2016-06-08 01:42 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description john@jbeidl.com 2016-06-07 22:27:52 UTC 
I have used the following for years with Microsoft Excel to locate the last used cell in a vertical column:

=LOOKUP(2,1/(B7:B1528<>""),B7:B1528)

When I use LibreOffice CALC on those Excel files, the cell contains the following #DIV/0!

I have a work around for CALC, however this requires me to modify all of the files that use the Excel method.
Comment 1 m_a_riosv 2016-06-08 01:42:25 UTC 
Please take a look to:

https://forum.openoffice.org/en/forum/viewtopic.php?t=59647#p263997

something like:
=SUMPRODUCT(MAX(ROW(B7:B1528)*NOT(ISBLANK(B7:B1528))))
or
=MAX(ROW(B7:B1528)*NOT(ISBLANK(B7:B1528))) entered as array
works for me, i guess also in excel.

It's unsupported a way of use LOOKUP() in LibreOffice, not a bug.