Bug Hunting Session
Bug 94540 - EDITING: VLOOKUP wrong result #VALUE in CALC 5.0.2.2
Summary: EDITING: VLOOKUP wrong result #VALUE in CALC 5.0.2.2
Status: RESOLVED DUPLICATE of bug 94924
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.2.1 rc
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2015-09-27 00:10 UTC by Robert Gonzalez MX
Modified: 2015-10-11 06:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Calc test file (61.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-09-27 00:12 UTC, Robert Gonzalez MX
Details
screenshoot of windows 10 (111.46 KB, image/png)
2015-09-27 01:50 UTC, Robert Gonzalez MX
Details
Screenshot on Linux OpenSuse 13.2 (164.57 KB, image/png)
2015-09-27 03:43 UTC, Robert Gonzalez MX
Details
Windows XP SP3 screenshoot (331.99 KB, image/jpeg)
2015-09-29 21:32 UTC, Robert Gonzalez MX
Details
Screenshoot LO 5.1 20150927 (158.85 KB, image/png)
2015-10-01 21:25 UTC, Robert Gonzalez MX
Details
Screeshoot LO 5.1 20151001 (150.92 KB, image/png)
2015-10-01 21:26 UTC, Robert Gonzalez MX
Details
Screenshoot LO 4.4.5 on Win10 works fine (143.43 KB, image/png)
2015-10-01 21:30 UTC, Robert Gonzalez MX
Details
vlookup_5_0_2_2_and_4_4_5_2_win7_64bit (200.89 KB, image/jpeg)
2015-10-03 07:23 UTC, Oliver Brinzing
Details
VLOOKUP 5.0 5.01 and 5.02 Win 10 (130.30 KB, image/png)
2015-10-03 16:41 UTC, Robert Gonzalez MX
Details
Same test file for changes (52.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-11 03:11 UTC, Robert Gonzalez MX
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Gonzalez MX 2015-09-27 00:10:46 UTC
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.
Comment 1 Robert Gonzalez MX 2015-09-27 00:12:02 UTC
Created attachment 119060 [details]
Calc test file
Comment 2 MM 2015-09-27 00:51:13 UTC
Unconfirmed with v5.0.2.2 under mint 17.2 x64.
I don't see any #VALUE's
Comment 3 Robert Gonzalez MX 2015-09-27 01:50:45 UTC
Created attachment 119061 [details]
screenshoot of windows 10
Comment 4 Robert Gonzalez MX 2015-09-27 03:42:35 UTC
Tested on OpenSuse 13.2 and is not present.
With LO 5.0.2.2

adding a screenshoot
Comment 5 Robert Gonzalez MX 2015-09-27 03:43:51 UTC
Created attachment 119062 [details]
Screenshot on Linux OpenSuse 13.2
Comment 6 Oliver Brinzing 2015-09-27 07:32:25 UTC
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
Comment 7 Buovjaga 2015-09-28 13:51:55 UTC
NEW per comment 6.
Comment 8 Robert Gonzalez MX 2015-09-29 21:31:06 UTC
I tested it today on Windows XP SP3 and the problem is not present.

adding screenshoot.
Comment 9 Robert Gonzalez MX 2015-09-29 21:32:01 UTC
Created attachment 119126 [details]
Windows XP SP3 screenshoot
Comment 10 Buovjaga 2015-10-01 11:08:34 UTC
Yep, confirmed it works ok.

Win 7 Pro 64-bit, Version: 5.0.2.2 (x64)
Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe
Locale: fi-FI (fi_FI)
Comment 11 Robert Gonzalez MX 2015-10-01 21:24:32 UTC
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
Comment 12 Robert Gonzalez MX 2015-10-01 21:25:27 UTC
Created attachment 119179 [details]
Screenshoot LO 5.1 20150927
Comment 13 Robert Gonzalez MX 2015-10-01 21:26:05 UTC
Created attachment 119180 [details]
Screeshoot LO 5.1 20151001
Comment 14 Robert Gonzalez MX 2015-10-01 21:30:05 UTC
Created attachment 119181 [details]
Screenshoot LO 4.4.5 on Win10 works fine
Comment 15 Buovjaga 2015-10-02 06:13:52 UTC
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)
Comment 16 Oliver Brinzing 2015-10-03 07:23:01 UTC
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
Comment 17 Oliver Brinzing 2015-10-03 07:23:41 UTC
Created attachment 119229 [details]
vlookup_5_0_2_2_and_4_4_5_2_win7_64bit
Comment 18 Buovjaga 2015-10-03 14:33:40 UTC
Let's set to NEW again.
Comment 19 Robert Gonzalez MX 2015-10-03 16:39:14 UTC
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.
Comment 20 Robert Gonzalez MX 2015-10-03 16:41:01 UTC
Created attachment 119242 [details]
VLOOKUP 5.0 5.01 and 5.02 Win 10
Comment 21 Jacques Guilleron 2015-10-10 08:16:47 UTC
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?
Comment 22 Robert Gonzalez MX 2015-10-11 03:09:54 UTC
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
Comment 23 Robert Gonzalez MX 2015-10-11 03:11:23 UTC
Created attachment 119495 [details]
Same test file for changes
Comment 24 Jacques Guilleron 2015-10-11 06:07:40 UTC
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 ***