Bug 71800 - Wrong result from LOOKUP
Summary: Wrong result from LOOKUP
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: raal
Depends on:
Reported: 2013-11-19 17:43 UTC by Mik44
Modified: 2015-05-06 14:24 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

A spreadsheet to generate a printed diary, with fixed entries listed on a second sheet (32.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-19 17:43 UTC, Mik44
Freshly-created example. (8.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-21 13:39 UTC, Mik44
try 2 (23.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-08-29 13:35 UTC, raal

Note You need to log in before you can comment on or make changes to this bug.
Description Mik44 2013-11-19 17:43:34 UTC
Created attachment 89482 [details]
A spreadsheet to generate a printed diary, with fixed entries listed on a second sheet

Build ID: 40b2d7fde7e8d2d7bc5a449dc65df4d08a7dd38
Spreadsheet function LOOKUP
works as expected except that empty "looked-up" cells display "30" in the result cell; non-empty entries are copied correctly to the result cells.

Any text entry added to the looked-up array clears ALL the unwanted 30s.
I wondered whether empty cells were being read as numbers and the result somehow appearing in hex. The look-up array had indeed been formatted as "number". Correcting this to 'text' format made no difference.
Adding any text, even one space, anywhere in the "look-up" array, even if then deleted, clears all the 30s. By this means I was able to generate a pdf output and print the file. Without that manoeuvre, the 30s appeared in the pdf and thus the print-out.

Is it a clue that the first week of this calendar file does not suffer the error?

Saving the "corrected" ods file and re-opening it brings back the unwanted 30s.

I attach this file, a 2014 calendar, stripped of the personal info.
Comment 1 Mik44 2013-11-19 19:14:53 UTC
Please ignore the line beginning "Is it a clue...". In the submitted file, that group of cells did not (but should) contain the LOOKUP function! Apologies.
Comment 2 Tim Lloyd 2013-11-21 03:03:02 UTC
Hi Mik. I agree with all your findings. I was able to recreate the problem using your file.

Can you advise how you created the doc pls? I had several attempts to recreate this scenario (Windows 7 & Fedora Linux) and every time the lookup worked as expected. IE. blanks display blanks.

Is it possible for you to create the doc from scratch and recreate the problem?
Comment 3 Dominique Boutry 2013-11-21 09:25:18 UTC
LibO on Win7 : Tentative explanation :
- the LibO help says ("handling of empty cells") that empty cells were filled with 0 or "" in earlier OOo/LibO versions ; I suppose it was 0 in the present case,
- in the "cal" sheet, concerned cells show a format of "Date/31" (only the calendar day-in-month)
- applying this format to 0 leads to a value of 30 (with default initial date : day 0 is the 30th of december 1899 ; that's OK)
- last supposition : there is no automatic recalculate after a loading.

If above considerations are true, Not A Bug, simply loading of a calendar saved with a really older OOo/LibO version.
I would just recommend to change the "Date/31" format with a "Text" one.
Comment 4 Mik44 2013-11-21 13:39:15 UTC
Created attachment 89586 [details]
Freshly-created example.

Many thanks to Tim and Dominique for their thoughts on this.
The file has been evolving over several years, and was probably first created as an xls file. I will check when I have access to the computer that holds this history, and do some experiments.

Interesting observation of Dominique's. Changing the formatting to "text" makes the error show as '0' instead of '30', results which also appear as the current example in the cell formatting box even after the error has been cleared from the result cells by the trick described initially.

However, experimenting now (on a laptop running XP Home and LO ver produces the error, appearing as '0' in a text or number-formatted result cell. It's more persistent, too, clearing only if the source cells are left with a space in them. (File attached: try.ods)
Comment 5 Mik44 2013-12-09 12:32:25 UTC
What should happen to this now? There have been no further entries for a while. The latest upload, "try.ods", was created afresh on a Windows XP laptop, and exhibits the problem in a much smaller and simpler file. It doesn't, however, appear in the attachment list as I see it on this page. Maybe I've done something wrong on this website.
Comment 6 raal 2014-08-29 13:35:57 UTC
Created attachment 105433 [details]
try 2

I opened file try.ods in LO 3.4.3 . Result of LOOKUP function is 0 or empty (where data is empty or space). I repaired data ranges in your testing file. I mean this is correct, see help
Setting as NEEDINFO. Please test with newer version, set back to UNCONFIRMED if you find the bug, otherwise close the bug. Thank you.
Comment 7 QA Administrators 2015-04-01 14:47:37 UTC
Dear Bug Submitter,

This bug has been in NEEDINFO status with no change for at least
6 months. Please provide the requested information as soon as
possible and mark the bug as UNCONFIRMED. Due to regular bug
tracker maintenance, if the bug is still in NEEDINFO status with
no change in 30 days the QA team will close the bug as INVALID
due to lack of needed information.

For more information about our NEEDINFO policy please read the
wiki located here:

If you have already provided the requested information, please
mark the bug as UNCONFIRMED so that the QA team knows that the
bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team
Comment 8 QA Administrators 2015-05-06 14:22:01 UTC
Dear Bug Submitter,

Please read this message in its entirety before proceeding.

Your bug report is being closed as INVALID due to inactivity and
a lack of information which is needed in order to accurately
reproduce and confirm the problem. We encourage you to retest
your bug against the latest release. If the issue is still
present in the latest stable release, we need the following
information (please ignore any that you've already provided):

a) Provide details of your system including your operating
   system and the latest version of LibreOffice that you have
   confirmed the bug to be present

b) Provide easy to reproduce steps – the simpler the better

c) Provide any test case(s) which will help us confirm the problem

d) Provide screenshots of the problem if you think it might help

e) Read all comments and provide any requested information

Once all of this is done, please set the bug back to UNCONFIRMED
and we will attempt to reproduce the issue. Please do not:

a) respond via email 

b) update the version field in the bug or any of the other details
   on the top section of our bug tracker

-- The LibreOffice QA Team 

This INVALID Message was generated on: 2015-05-06

Warm Regards,
QA Team