Created attachment 127213 [details] Spreadsheet showing bug If you construct a LOOKUP function to search for a null value, such as: =LOOKUP("",Range_B,Range_A) 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!) THANKS! JK
Created attachment 127214 [details] PDF showing output from LibreOffice
Created attachment 127215 [details] PDF showing OpenOffice works as expected
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.
No repro in Version: 5.3.0.0.alpha0+ 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)
Created attachment 127232 [details] screenshot of correct calculation
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 ***
Also tested against Version: 5.2.0.4 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.
@Jon : please test with the latest production release of LO on OSX.
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... Version: 5.2.1.2 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. THANKS! JK
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: Version: 5.3.0.0.alpha0+ 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
Created attachment 127237 [details] Screenshot of lookup in dev 5.3
Adding keyword 'bibisectRequest'.
I can't reproduced it in Version: 5.3.0.0.alpha0+ 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?
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.
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.
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 https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#LOOKUP "If Find is of type Text and the value found is of type Number, the #N/A Error is returned."