Tested with LibreOffice Version: 5.0.2.2 Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe Locale: es-MX (es_MX) On Windows 10 Description: Function VLOOKUP en LO Calc 5.0.2 display #VALUE instead of the correct result. In LO 4.4.5 works correct. Steps to reproduce Open test file in LO Calc 4.4.5 and see the yellow column named “ciclo” (cycle) the result is a number of cycle depending on the date cycle 1 = C1, cycle 2 = C2, etc. the table of this is in the “parametros” (parameters) sheet. In LO Calc 5.0.2 the result of the function displays #VALUE, instead of the correct result.
Created attachment 119060 [details] Calc test file
Unconfirmed with v5.0.2.2 under mint 17.2 x64. I don't see any #VALUE's
Created attachment 119061 [details] screenshoot of windows 10
Tested on OpenSuse 13.2 and is not present. With LO 5.0.2.2 adding a screenshoot
Created attachment 119062 [details] Screenshot on Linux OpenSuse 13.2
i can confirm the problem with a debug build of lo 5.1.0.0.alpha1+ build id e2c7ef060397c4e0a0297b14b008596fb1c089a4 and LibreOffice_5.0.2.1_Win_x86 on win 7/64bit. if one edit a #values formula, e.g. from: =SVERWEIS(A288;Parametros.$C$3:$E$14;3) to: =SVERWEIS($A288;Parametros.$C$3:$E$14;3) the value C4 is show
NEW per comment 6.
I tested it today on Windows XP SP3 and the problem is not present. adding screenshoot.
Created attachment 119126 [details] Windows XP SP3 screenshoot
Yep, confirmed it works ok. Win 7 Pro 64-bit, Version: 5.0.2.2 (x64) Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe Locale: fi-FI (fi_FI)
Hi. What I ment with my comment 8 on the test on Windows XP is that is not reproducible on that OS. But it is still present on Windows 10. Today I've made this tests and it is present Test with Version: 5.1.0.0.alpha1+ (x64) Build ID: 09fc6fef2d03ca8558dd6f0eec45d61ceb282cb5 TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-09-27_22:33:48 Locale: es-MX (es_MX) On Windows 10 Is present Test with Version: 5.1.0.0.alpha1+ Build ID: 25de5cfa43b2b1cb7d7214470acc7719839e13fe TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-01_08:49:54 Locale: es-MX (es_MX) On Windows 10 It is present I'm sending screenshoots of this. Another finding is that formula on cell L3 =VLOOKUP(A3,Parametros.$C$3:$E$14,3) looks for the value on A3 in the same row. But if you change the formula on cell L3 =VLOOKUP(A2,Parametros.$C$3:$E$14,3), looking for the value of A2, one row above, it displays the result of the function, but that's not what it should do. It seems that looking for a cell in the same row, is not working So I believe to change the status to unconfirmed so more tests can be made from others to confirm
Created attachment 119179 [details] Screenshoot LO 5.1 20150927
Created attachment 119180 [details] Screeshoot LO 5.1 20151001
Created attachment 119181 [details] Screenshoot LO 4.4.5 on Win10 works fine
Works fine here. Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+ Build ID: 25de5cfa43b2b1cb7d7214470acc7719839e13fe TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-01_08:49:54 Locale: en-US (fi_FI)
as mentioned above, it's not working with lo 5.0.2.2 on my notebook win7 pro 64-bit, but lo 4.4.5.2. is fine. adding a screenshot showing both result
Created attachment 119229 [details] vlookup_5_0_2_2_and_4_4_5_2_win7_64bit
Let's set to NEW again.
I have tested it with Version: 5.0.0.5 Build ID: 1b1a90865e348b492231e1c451437d7a15bb262b Locale: es-MX (es_MX) on Windows 10 and the problem is not present. Also tested it with Version: 5.0.1.2 Build ID: 81898c9f5c0d43f3473ba111d7b351050be20261 Locale: es-MX (es_MX) on Windows 10 and also the problem is not present. See attachment.
Created attachment 119242 [details] VLOOKUP 5.0 5.01 and 5.02 Win 10
Hi all, I confirm the issue with LO 5.1.0.0.alpha1+ Build ID: d417059dae303685de7aa0f4b1c192ffcf5304d5 TinderBox: Win-x86@39, Branch:master, Time: 2015-09-05_09:23:01 Locale: fr-FR (fr_FR) Windows 7 Home Also with LO 5.0.2.2 Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe LO 5.0.2.1 Build ID: 9a18d52abbdfbdc2ac9acebec2b92e7859eb73b7 Works with LO 5.0.1.2 Build ID: 81898c9f5c0d43f3473ba111d7b351050be20261 The issue disappears if I reduce the numbers of lines of the Calc test file to exactly one hundred. do you reproduce too?
Hello. I can reproduce the problem in Version: 5.0.2.2 Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe Locale: es-MX (es_MX) on Windows 10 Version: 5.1.0.0.alpha1+ Build ID: f830600ece806ec365a4839e79afabe183c5e36d-GL TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-06_22:49:09 Locale: es-MX (es_MX) On windows 10 Steps Enter on L2 =VLOOKUP(A2,Parametros.C3:E14,3), correct result displays Add absolute reference to C3:E14 L2 =VLOOKUP(A2,Parametros.$C$3:$E$14,3) correct result displays Save and close file open again and the result is preserved copy L2 with ctrl – c paste to range L3:L100 (98 rows) with ctrl -v correct result displays save and close the file open again and the result is preserved Erase the formulas and start again, but paste 99 rows to range L3:L101 #VALUE error displays After the pasting click in the undo arrow or ctrl – z The first formula entered that was displaying correctly, now displays a #VALUE error If the copiying is applied by parts smaller than 99 rows the correct result displays but when the file is saved, closed and opened again The #VALUE error displays
Created attachment 119495 [details] Same test file for changes
Ok. In Tools > Options > LibreOffice > OpenCL uncheck Allow use of OpenCL. Open your file with those settings. Works. DUPLICATE of Bug 94924 - strange behavior in formulas computing *** This bug has been marked as a duplicate of bug 94924 ***