Created attachment 121951 [details] vlookup file whe it is used in many rows I have a Calc file with two pages. The second one use VLOOKUP on the first page. The original file was created in v4.3.5.2 and it's work correctly. When I open the file in v5.0.4.2 doesn't work correctly. It changes all the correct values (text) to numbers. It doesn't work in version 5.0 compiled at 14/01/2016 In the attached file you must go to the second page, I use VLOOKUP at column G. If you copy one cell that use VLOOKUP to other cell in the same column (or int he same cell) it changes all the values in that column to numbers. If you modify the function it's work correctly again (Try to put an space before the parenthesis)
I don't check all the 17000+ VLOOKUP formula, but I don't see any errors. With 5.0.4, nor with 5.2.0alpha Please give more precise information. What cell is incorrect, what do you expect and what is the (incorrect) result?
Reproducible. Win10x64 Version: 5.0.4.2 (x64) Build ID: 2b9802c1994aa0b7dc6079e128979269cf95bc78 For me disabling OpenCL solves the issue. Menu/Tools/Options/LibreOffice/OpenCL - Unmark "Allow use of OpenCL"
(In reply to GerardF from comment #1) > I don't check all the 17000+ VLOOKUP formula, but I don't see any errors. > With 5.0.4, nor with 5.2.0alpha > > Please give more precise information. > What cell is incorrect, what do you expect and what is the (incorrect) > result? It's not necesary to check all rows, copying only one cell changes all the values of the column from the code returned from page "Control" to a numeric value. I have Windows [Versión 6.1.7601] I proved it on others computers in my company and it works in some computers and fail in others computers. I disabled OpenCL and it works correctly.
I supose it's not a bug of LibreOffice, it's a problem with OpenCL
Not so quick, Jesús. :) The code to every function for use with OpenCL is done inside LibreOffice. https://gerrit.libreoffice.org/gitweb?p=core.git&a=search&h=HEAD&st=commit&s=opencl+vlookup
@mmeeks: This is yet another "calculating wrong with OpenCL" report. Any estimate if, how and in what time frame it can be fixed on your side? As an interim measure I suggest to default the OpenCL option to disabled in all releases.
Jesus, could you please answer exactly to the question in comment #1? What cell is incorrect, what do you expect and what is the (incorrect) result? It is enough to point to one cell to look at.
Created attachment 122254 [details] An example of the error with pictures
I guess the cells in the G column on the Hoja2 sheet should not contain numbers, even after a hard recalc (Ctrl+Shift+F9)? In that case, this seems to be fixed in the 5.1 and master branches. I also checked the TDF build 5.1.0.2, is also fixed there. Sadly not fixed in 5.0.5.1, though.
Cherry-picking commit 476bef70f1d9fd58b29a1f6fb95e54567b031acf to the 5.0 (and 5.0.5) branches would fix it. Submitting such requests to gerrit.
https://gerrit.libreoffice.org/#/c/21874/ https://gerrit.libreoffice.org/#/c/21875/
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f50edb1aabaf9de37782e63abd109e2276bd0c4&h=libreoffice-5-0 tdf#97150: tdf#94924: If we can't handle strings, don't try to then It will be available in 5.0.6. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=320246d24d8a07cdb6b8400e8afa48696cc39343&h=libreoffice-5-0-5 tdf#97150: tdf#94924: If we can't handle strings, don't try to then It will be available in 5.0.5. 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.
Hi @Tor, in the patch there is comment: "And since a while, we don't claim to handle strings in VLOOKUP. Which is true." Please forgive me, but I can't find any place with this assertion, and don't seems such from this release note: https://wiki.documentfoundation.org/ReleaseNotes/4.2#Calc_2 - second point. And overall I hope it's not a target, it should make calc unusable, it's one of more used functions, at least my impression after a lot of year in forums. There is a unsolved regression with the slowness searching for strings when the option for "search criteria for = and <> must apply to whole cells" is disable for VLOOKUP. fdo#79892
My comment of course meant that we don't handle strings in the OpenCL implementation of VLOOKUP. See (for the 5-0 branch) commit 3253cc2b4ab547cc42cb9f62254ecccac40b5459 where the takeString() virtual member function of the sc::OpenCL::OpVLookup class was dropped.
Thanks Tor, sorry for my bad interpretation.