Bug Hunting Session
Bug 73113 - LOOKUP function results in #N/A
Summary: LOOKUP function results in #N/A
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.1 rc
Hardware: All All
: highest critical
Assignee: Eike Rathke
URL:
Whiteboard: target:4.3.0 target:4.2.0.2
Keywords: bibisected, regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2013-12-29 10:05 UTC by andis.lazdins
Modified: 2015-12-15 22:21 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file with not working lookup function (61.78 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-29 10:05 UTC, andis.lazdins
Details
Sample file regarding lookup function bug (21.07 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-03-13 11:40 UTC, Burak Ural
Details

Note You need to log in before you can comment on or make changes to this bug.
Description andis.lazdins 2013-12-29 10:05:39 UTC
Created attachment 91272 [details]
Sample file with not working lookup function

I found that LOOKUP function is not working in many files created in previous versions of Libreoffice. A file attached to this report is an example of merging 2 datasets using LOOKUP function. In sheet "Kopa" there is LOOKUP, which is still working and in sheet "C" there is LOOKUP in column E, which is not working any more in 4.2.0.1 (showing #N/A for me in all rows, except cell E37, where correct result is shown). In 4.1.4.2 with the same locale settings (Latvian) everything works fine.
Sheet "C"  column F contains SUMIF function, which works fine with the same search criteria.
I found similar problem with not working DAVERAGE function in previously created files.

System: Ubuntu 13.04. 32 bit.
Comment 1 m.a.riosv 2013-12-29 13:16:45 UTC
Hi Andis, thanks for reporting.

Confirmed.
Win7x64Ultimate.
Version: 4.2.0.1.0+ Build ID: 241eef7ca9b863ceb1f7f457a4bb2931b5d37f17
         TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2013-12-28_23:19:40

Last version working:
Version: 4.1.5.0.0+ Build ID: 56381a9b28dbe4caf6e3d0a92dfddcddcebe349

Regression and a blocker for 4.2, broke calculations with a common and basic function.
Comment 2 m.a.riosv 2013-12-29 13:17:53 UTC
In relation with Database function, there is a reported bug:
https://bugs.freedesktop.org/show_bug.cgi?id=72949
Comment 3 Jorendc 2013-12-29 13:34:50 UTC
So confirmed regression, non-OS specific (platforms -> all).
Comment 4 Joel Madero 2013-12-30 19:25:09 UTC
c64ee04f962f148e5496ee63c1b85f5339d0cecd is the first bad commit
commit c64ee04f962f148e5496ee63c1b85f5339d0cecd
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Thu Oct 17 14:30:14 2013 +0000

    source-hash-022c54742e7997bf46a608f1ab0b500f2537f7f5
    
    commit 022c54742e7997bf46a608f1ab0b500f2537f7f5
    Author:     Tor Lillqvist <tml@iki.fi>
    AuthorDate: Tue Jun 25 07:19:41 2013 +0300
    Commit:     Tor Lillqvist <tml@iki.fi>
    CommitDate: Tue Jun 25 07:19:41 2013 +0300
    
        WaE: private field 'mrCells' is not used
    
        Change-Id: I0ab3fabb82c839f5194b0e20eb834dd86635a609

:100644 100644 4b10c5c8ddbedca0971e0839a8acc603792a447c 483b58760a06de929b32eafde25a67466c622502 M	ccache.log
:100644 100644 54c63dd94c275598f317bb54ddfdd27aaad5d8a1 fcfaf4eddaf5f8c7a66f90a052cbf2c7473cdc9b M	commitmsg
:100644 100644 e607019f9ceabe4513be6de63f5724c67ece57f9 3e023e83e964fd4b90d7bdf45eab489c7382956c M	dev-install.log
:100644 100644 2d16d57e331ca5fab2ec46ad12fe030528c544bb 47ead046b9af75e2384d8d8f51767edfa54d5dc8 M	make.log
:040000 040000 3aaab4081e7400904dc31731c74182db7e18493c 82a20807f2d069e8294cfa6e30778214a869a341 M	opt


# bad: [25428b1e953636f74986622c5df614f04c150ed1] source-hash-cb4e009c4539c535108021934e545194b35cad9d
# good: [f0f6c65eb764f0303f59c58d320e9b0d5a894377] source-hash-4b9740b4ec3987e1d4d2ad6d20b4dcf996a4fa2e
git bisect start 'latest' 'oldest'
# good: [a72833796a7e527d9efc9ca6d8fe9b579e469105] source-hash-1472b5f87314fe660ef1a7b254e51272669f12f6
git bisect good a72833796a7e527d9efc9ca6d8fe9b579e469105
# bad: [b21386bf459ae47bd6e461ea94cea6a06729a1ff] source-hash-570fe620e9d573cfc9fc260e6518563c6a6c1a3c
git bisect bad b21386bf459ae47bd6e461ea94cea6a06729a1ff
# good: [8febbf1f26867388acf1d005b58978cbe4130d16] source-hash-7275a051677b5646e56623b3addc783880ce8e9b
git bisect good 8febbf1f26867388acf1d005b58978cbe4130d16
# bad: [3897b261df824b9e25e5226d4fc17f28bfca2274] source-hash-61db96daa87754af24355d7ac94ee0305f22ff87
git bisect bad 3897b261df824b9e25e5226d4fc17f28bfca2274
# good: [cd4dab4f7cd1e732b0a3ca1eaadb9d52e6863867] source-hash-b139f6fedfcf3cbed0eadeb007e2155b576413d2
git bisect good cd4dab4f7cd1e732b0a3ca1eaadb9d52e6863867
# bad: [c133035ff38a0a861975e299b0debff65eff64d1] source-hash-344d80ee1d3829b28c18135ac4f0500d4b69aedd
git bisect bad c133035ff38a0a861975e299b0debff65eff64d1
# bad: [633649b99650518c34fa17096a08fdce1955e0a6] source-hash-4d5fc661d37d03129b8054e494c03bed1933231d
git bisect bad 633649b99650518c34fa17096a08fdce1955e0a6
# bad: [c64ee04f962f148e5496ee63c1b85f5339d0cecd] source-hash-022c54742e7997bf46a608f1ab0b500f2537f7f5
git bisect bad c64ee04f962f148e5496ee63c1b85f5339d0cecd
# first bad commit: [c64ee04f962f148e5496ee63c1b85f5339d0cecd] source-hash-022c54742e7997bf46a608f1ab0b500f2537f7f5
Comment 6 Kohei Yoshida 2013-12-30 21:16:19 UTC
Setting it to the right component.
Comment 7 Eike Rathke 2014-01-02 13:22:41 UTC
Taking.
Comment 8 Commit Notification 2014-01-02 19:59:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6046207ea2ef67de1388b30a7e8e0da5dcf5e6bf

resolved fdo#73113 not string cell does not mean no string at cell



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2014-01-02 20:01:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1d1301de3248b1e08a66a259457f59b5406a5034&h=libreoffice-4-2

resolved fdo#73113 not string cell does not mean no string at cell


It will be available in LibreOffice 4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2014-01-03 14:49:20 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=724a8ccd81567a2ad93111a9cac257eb7ec7819f

fdo#73113: Write unit test for LOOKUP.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 11 Burak Ural 2014-03-13 09:41:23 UTC
Dear Sirs,

I am using Libreoffice 4.2.1.1 in XP SP3.

I have document with lots of lookup functions created on openoffice 3.x releases.

I can confirm there is still bug in lookup function.

Lookup function still returns N/A 
The function works well on MS Excel and Openoffice but not on Libreoffice.

I kind you to ask to fix this matter a.s.a.p

Kind Regards,
Burak Ural
Comment 12 Eike Rathke 2014-03-13 10:32:10 UTC
@Burak:
Do you have a failing sample document you could attach?
Comment 13 Burak Ural 2014-03-13 11:40:47 UTC
Created attachment 95714 [details]
Sample file regarding lookup function bug

Lookup function works only if all data is current sheet. If data is called from another sheet , it fails.
Using Libreoffice 4.2.1.1 on Windows XP SP3.
Comment 14 Burak Ural 2014-03-13 11:42:04 UTC
I uploaded a sample file in my previous post just now.

Please test yourself.
Kind Regards,
Burak Ural
Comment 15 Kohei Yoshida 2014-03-13 12:42:37 UTC
It's better to file a new bug for a new test case. Reopening should be done only when the original test case fails.  But I'll leave the decision up to Eike.
Comment 16 m.a.riosv 2014-03-13 12:57:38 UTC
Hi Burak,

search vector must be sorted,
(https://help.libreoffice.org/scalc/SC_HID_FUNC_VERWEIS?Language=en-US&System=WIN&Version=4.2#bm_id3152877),
and your are looking in the wrong column, data for search vector are in Sheet2.B not in Sheet2.A

with data sorted by Sheet2.B, your formula should be:
G3: =LOOKUP(F3;Sheet2.$B$3:$B$16;Sheet2.$A$3:$A$16)

For me not a bug. If you are not agree, please as Kohei has suggested file a new bug.

IMO, Eike can reset the status to Resolved Fixed.
Comment 17 Burak Ural 2014-03-13 13:21:02 UTC
I am sorry , I made some error during creating a sample file.

I did test and confirm :

1- Even unsorted it is working.

2- It is working if the data is in different sheet.

3- It is NOT working if the search area is bigger actual area filled with data.

For example:

1  a  z
2  b  x
3  c  y
4  d  v
5  e  t

If I use formula =lookup(a1;b1:b100;c1;c100) - imagining I will enter more data in future - so I set search area much longer b100 instead of b5 , the result becomes N/A.

Other spreadsheet software does not get affected of empty cells.
Would you please classify this as a bug and fix it?

Sorry for the previous confusion but I just found out why exactly my calculations were not working.

Kind Regards,
Burak Ural
Comment 18 Burak Ural 2014-03-13 13:24:22 UTC
a1;b1:b100;c1;c100  = > a1;b1:b100;c1:c100

Typing mistake ; instead of :
Just in case if you think I used a wrong formula.
The bug exists if the search area is bigger than actual data area.
Comment 19 Kohei Yoshida 2014-03-13 14:36:24 UTC
(In reply to comment #18)
> a1;b1:b100;c1;c100  = > a1;b1:b100;c1:c100
> 
> Typing mistake ; instead of :
> Just in case if you think I used a wrong formula.
> The bug exists if the search area is bigger than actual data area.

That's an entirely different issue.  I'll close this bug once again.

Try again once Bug 75642 finds its way into the stable branch.  You are still welcome to file a new bug with your test case too then we can verify if that's really a duplicate of Bug 75642.
Comment 20 Burak Ural 2014-03-13 16:05:15 UTC
After doing some more tests, I can confirm this problem I have , is definitely specific to the file I work with.

Please there are other formulas with empty cells and they are working fine.

Whatever the reason do not think anymore it is not about formulas.

In my document there are 406 lines with data.
I have added more data and after 500 lines , the formula began to work.

I could not produce the same situation with entering data to a new sheet and creating same number of data using a lookup function that has more search area defined.

Sorry for taking your time.
Comment 21 Eike Rathke 2014-03-14 19:12:35 UTC
It is likely that your problem is an incarnation of one of the other VLOOKUP bugs fixed for 4.2.2/4.2.3.

Setting this one RESOLVED again.
Comment 22 Burak Ural 2014-03-15 09:56:39 UTC
Unfortunately that is not so.

I mean it has nothing to do with Vlookup bug fixed.

This is something like for example the page preview disapperance bug.
Sometimes, while working on Calc, I place a picture into the document.
I click print preview and all I get is an empty page.
I copy whole sheet into another sheet and it gets normal.

There is something wrong within the depths of openoffice/libreoffice code that fails sometimes for some reason...

In this case, I have a long xls with 5 sheets and some formulas.
The last sheet has the calculation formula from other sheets and while other sheets has 400 and 700 lines, the formula covers 1000 lines of other sheets.
Interestingly in Libreoffice 4.1.2.2 , the lookup formula referencing 2nd sheet works fine. Lookup formula referencing first sheet was giving N/A until I filled up to 500 lines with data and deleted the data returning to previous stage, however the lookup code worked fine after this.

Yesterday, I downloaded 4.2.3.1 release current on the main download page.
I wanted to test the original xls to see how the lookup code would react.
Interestingly this time even the previous working lookup referencing 2nd sheet did not work.
I again entered junk data up to 1000 lines and removed and it worked fine.
However this only happens with my xls sheet. I can not produce it with a small self made sheet. So something out there , I have no idea.

Regards,
Burak Ural
Comment 23 m.a.riosv 2014-03-15 11:47:23 UTC
Pleae Burak, could you hear what developers said and have a bit of confidence in them.
Looking in the bug https://bugs.freedesktop.org/show_bug.cgi?id=75642 as in the comment #19 Kohei have informed,
In that bug the last patch is 2014-03-13 15:43:04 UTC 
4.2.3.1 was compiled (Windows) 13-Mar-2014 21:15
so it's not possible the patch is in 4.2.3.1

I have verified it is fixed in daily releases:
Version: 4.2.4.0.0+ Build ID: b5b9da46ceae23b25e963087d00b0ae5b4785c93
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-03-13_23:32:39
Version: 4.3.0.0.alpha0+ Build ID: 12ae7672f285da1d4c730315e8db23b3396b71cc
   TinderBox: Win-x86@39, Branch:master, Time: 2014-03-14_00:18:00

Please have a bit of patience (the patience is the mother of the science) and stop adding new comments in this report, it doesn't help in any way.
Comment 24 Robinson Tryon (qubit) 2015-12-15 22:21:31 UTC
Migrating Whiteboard tags to Keywords: (bibisected)
Remove redundant 'ConfirmedRegression'
[NinjaEdit]