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.
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.
In relation with Database function, there is a reported bug: https://bugs.freedesktop.org/show_bug.cgi?id=72949
So confirmed regression, non-OS specific (platforms -> all).
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
Corresponding range: http://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=b139f6fedfcf3cbed0eadeb007e2155b576413d2..022c54742e7997bf46a608f1ab0b500f2537f7f5
Setting it to the right component.
Taking.
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.
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.
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.
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
@Burak: Do you have a failing sample document you could attach?
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.
I uploaded a sample file in my previous post just now. Please test yourself. Kind Regards, Burak Ural
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.
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.
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
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.
(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.
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.
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.
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
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.
Migrating Whiteboard tags to Keywords: (bibisected) Remove redundant 'ConfirmedRegression' [NinjaEdit]