Download it now!
Bug 101988 - LOOKUP of null value returns #NA
Summary: LOOKUP of null value returns #NA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Eike Rathke
Keywords: bibisectRequest, regression
Depends on:
Reported: 2016-09-08 10:58 UTC by Jon R Kibler
Modified: 2016-10-18 12:41 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Spreadsheet showing bug (16.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-08 10:58 UTC, Jon R Kibler
PDF showing output from LibreOffice (16.32 KB, application/pdf)
2016-09-08 10:59 UTC, Jon R Kibler
PDF showing OpenOffice works as expected (26.21 KB, application/pdf)
2016-09-08 11:00 UTC, Jon R Kibler
screenshot of correct calculation (212.23 KB, image/png)
2016-09-09 12:26 UTC, Alex Thurgood
Screenshot of lookup in dev 5.3 (117.76 KB, image/png)
2016-09-09 20:49 UTC, MM

Note You need to log in before you can comment on or make changes to this bug.
Description Jon R Kibler 2016-09-08 10:58:21 UTC
Created attachment 127213 [details]
Spreadsheet showing bug

If you construct a LOOKUP function to search for a null value, such as: 


in LibreOffice, it returns a value of: #N/A

The function works as expected in Excel and OpenOffice, returning the last value in Range_A for which there is not a null value in Range_B.

Please see attached spreadsheet for a simple example of the bug.

I will update bug report with PDFs showing the difference between OpenOffice and LibreOffice results. (Since you apparently can't attach multiple files!)


Comment 1 Jon R Kibler 2016-09-08 10:59:26 UTC
Created attachment 127214 [details]
PDF showing output from LibreOffice
Comment 2 Jon R Kibler 2016-09-08 11:00:28 UTC
Created attachment 127215 [details]
PDF showing OpenOffice works as expected
Comment 3 MM 2016-09-09 12:19:18 UTC
Confirmed with v5.2.1.2 under ubuntu 16.04 x64.
Confirmed with v4.1.6.2 under mint 16 x64.
Unconfirmed with v3.3.4 under windows 7 x64.
Comment 4 Alex Thurgood 2016-09-09 12:24:39 UTC
No repro in 

Build ID: c8dde9d36ffa9f6d9c092805796ec87624f1b2a8
Threads CPU : 2; Version de l'OS :Mac OS X 10.11.6; UI Render : par défaut; 
Locale : fr-FR (fr.UTF-8); Calc: group

I see the calculated result (cf. screenshot)
Comment 5 Alex Thurgood 2016-09-09 12:26:13 UTC
Created attachment 127232 [details]
screenshot of correct calculation
Comment 6 Alex Thurgood 2016-09-09 12:30:40 UTC
This should be a DUP of bug 75642, which was slated as fixed by Kohei's commit

*** This bug has been marked as a duplicate of bug 75642 ***
Comment 7 Alex Thurgood 2016-09-09 12:33:02 UTC
Also tested against

Build ID: 066b007f5ebcc236395c7d282ba488bca6720265
CPU Threads: 2; OS Version: Mac OS X 10.11.6; UI Render: default; 
Locale: en-GB (fr.UTF-8)

No repro there either.
Comment 8 Alex Thurgood 2016-09-09 12:34:58 UTC
@Jon : please test with the latest production release of LO on OSX.
Comment 9 Jon R Kibler 2016-09-09 13:44:40 UTC
As requested by: 
Comment # 8 on bug 101988 from Alex Thurgood
@Jon : please test with the latest production release of LO on OSX.

I installed the latest release version...

Build ID: 31dd62db80d4e60af04904455ec9c9219178d620
CPU Threads: 8; OS Version: Mac OS X 10.11.6; UI Render: default; 
Locale: en-US (en.UTF-8); Calc: group

THIS IS STILL AN ISSUE. I checked it with a fresh new example, and it is still broken.

Comment 10 MM 2016-09-09 20:48:15 UTC
Bug 75642 is about vlookup which was indeed fixed. *This* report is about lookup, which is still broken.

Can still confirm the bug with under ubuntu 16.04 x64:

Build ID: 2846aae8feee12695aa920d49c611c5c13cf6f5f
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-09-08_23:11:20
Locale: en-US (en_US.UTF-8); Calc: single
Comment 11 MM 2016-09-09 20:49:57 UTC
Created attachment 127237 [details]
Screenshot of lookup in dev 5.3
Comment 12 Xisco Faulí 2016-09-12 12:23:27 UTC
Adding keyword 'bibisectRequest'.
Comment 13 Xisco Faulí 2016-10-06 13:09:07 UTC
I can't reproduced it in

Build ID: ae3ec79354f7b4967e736c6a4cd7c08fc52e2b7d
CPU Threads: 4; OS Version: Linux 4.2; UI Render: default; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

What happens if you reset your profile?
Comment 14 MM 2016-10-13 23:43:31 UTC
Resetting the userprofile helps. Now the value is visible.
But you can reproduce it.

0 - reset user profile
1 - open document
2 - options > LO calc > Formula > Recalculation on file load > [set to] Always .....
3 - Close LO, When LO asks you to save, save the file (under a different name).
4 - Open LO / the file.

Now E2 will be set as #N/A.
Setting Recalculation on file load to never... again, close and open LO, load the file and the value is back again.
Comment 15 Eike Rathke 2016-10-17 21:52:10 UTC
Can simply be reproduced with a hard recalc Shift+Ctrl+F9 or by changing any value in Vrange or Drange or editing the formula expression.

Though unrelated to this "" empty string lookup, note that the search vector for LOOKUP must be sorted in ascending order, otherwise the function may deliver meaningless arbitrary results. This is also true for OpenOffice and probably Excel as well.

As for the "" empty string lookup, that should sort between the largest numeric value and any other string so the largest numeric value should be matched in this case IF the range was sorted properly.

I'll investigate.
Comment 16 Eike Rathke 2016-10-18 12:41:19 UTC
So, actually the claim that Excel would return the last numeric value if looking up an (empty) string is not true. Also Excel returns #N/A, even for the particular sample document attached to this bug.

Also ODF OpenFormula (ODFF) explicitly defines this behavior in
"If Find is of type Text and the value found is of type Number, the #N/A Error is returned."