Created attachment 181209 [details] Spreadsheet demostrating issue VLOOKUP function that is looking up a date, gives incorrect results for dates after April 21, 2022. Please refer to attached spreadsheet. Tab "FX" represents foreign currency exchange rates for 3 different currencies for each business day (weekends, holidays etc. are excluded). Everything works as expected up to April 21, 2022 date, and for all subsequent dates, the functions returns the value for April 21, 2022 which is incorrect. Strangely, if 2 rows are skipped, the same function gives the correct result again. I also observed that if I enter 2 more dates in tab "FX" as follows: In cell A752 “Apr 30, 2022” In cell A753 “May 1, 2022” Then all VLOOKUP functions start to give the correct result again.
Also just installed version 7.2.7.2 and the issue is still present. Previous version was 7.2.6.2.
Can repro on Version: 7.3.4.2 (x64) / LibreOffice Community Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-GB (en_US); UI: en-GB Calc: CL
The fourth parameter of the VLOOKUP function must be set to 0 if there are dates missing. Use =VLOOKUP($A1,$FX.$A$2:$FX.$D$1048576,4,0) FX page are missing days 23 and 24. 22/04/2022 25/04/2022 And 30/04/2021 is duplicated.
Please, dismiss my previous comment. Thanks. Can you confirm that the file was saved with LibreOffice? If that is not the case, you must change option for Recalculation on File Load to Prompt user or Ever recalculate (menu Tools - Options - LibreOffice Calc - Formula) Saving and reloading the file is not sufficient. A bit more test, and I find that Recalculate Hard (menu Data - Calculate) fix all the results. Pressing F9 fixes the result in the active cell, one at a time.
Whit me: Version: 7.2.3.2 / LibreOffice Community Build ID: 20(Build:2) CPU threads: 1; OS: Linux 5.3; UI render: default; VCL: gtk3 Locale: es-MX (es_ES.UTF-8); UI: en-US Calc: threaded
I confirm that the file was originally created with and saved with LibreOffice. No other application was ever involved with that file. Neither Recalculate Hard (Ctrl + Shift + F9) nor Recalculate (F9) produces correct results. Also note that the attached file is a small part of a larger file that I created (but can't attach for privacy reasons). It is a partial copy-paste from the larger file, and I can confirm that I have many VLOOKUP functions looking up many dates going back several years, and as far as I know, the results were always correct. The results became incorrect only when looking up April 22, 2022 date and later. Some dates are missing because only business days (Monday - Friday, excluding holidays) are on the spreadsheet. Normally, the formula returns the value of the closest last date which is available. For example, if we have the following dates: April 29, 2022 May 1, 2022 and the VLOOKUP is asked to look up April 30, 2022, it will give the value for April 29, 2022, which to me is acceptable behavior. Let me know if I've answered all your questions.
[Automated Action] NeedInfo-To-Unconfirmed
This behaves the same way for me with Version: 6.0.0.0.alpha1+ Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); Calc: group threaded and Version: 7.4.0.0.alpha1+ / LibreOffice Community Build ID: c1898a4d1b38e0077416a104e2b47573ce207ffa CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: CL
Scott, and anyone who's checking the bug please press Ctrl+Shift+F9 to recalculate the whole sheet. Unless the buggy values stay there, this bug isn't confirmed, because whatever is seen after opening are cached values coming from the spreadsheet file. Slavaf2000, you too, please press Ctrl+Shift+F9 to recalculate the whole sheet. Are the values still buggy? If not, is there a version where they remain buggy?
As I had commented above: "Neither Recalculate Hard (Ctrl + Shift + F9) nor Recalculate (F9) produces correct results." Please also note that I have another spreadsheet with a similar setup, and it's the same kind of error happening there, just with different dates and different rows in the FX tab. So I definitely think it's a bug! Many thanks to everyone who is or will be working on it! Your efforts are much appreciated.
Further to my previous comment, considering that the other spreadsheet that I have has a similar set up and is experiencing a the same issue, I suspect the VLOOKUP function breaks down when the referenced lookup range is large (in my case all the way to the bottom of the spreadsheet in the attached spreadsheet, and smaller but still large in my second spreadsheet), but the values to look up stop somewhere within this range (i.e. the lookup range is not filled with values entirely and there are empty cells in the bottom portion of the lookup range). It seems to work fine up until the last few rows that are filled in, and after than it just seems to stop looking further, and is returning a value close to the end of these filled rows, but it's not the right value. As a temporary workaround, if I enter a couple more dates in sequence at the end, then the function starts to give correct results. I think there must be some kind of an offset that is preventing looking up values all the way to the end of the filled out range. That is my suspicion based on the behavior. Play around with the spreadsheet as I described and you will see it for yourself!
Created attachment 181282 [details] Screenshot of additional behavior Thank you Aron for the hard recalculate function however the behavior still persists. If I copy & paste the vlookup formulas directly below the buggy rows it still doesn't function properly. Copy & pasting below with blank rows in between does function properly. Please see screenshot. I'm inclined to leave the status as "New" unless you have other ideas. Thanks!
(In reply to slavaf2000 from comment #10) > As I had commented above: "Neither Recalculate Hard (Ctrl + Shift + F9) nor > Recalculate (F9) produces correct results." Indeed, I missed that. Can you and/or Scott please paste the version information from Help -> About?
(In reply to Aron Budea from comment #13) > (In reply to slavaf2000 from comment #10) > > As I had commented above: "Neither Recalculate Hard (Ctrl + Shift + F9) nor > > Recalculate (F9) produces correct results." > Indeed, I missed that. Can you and/or Scott please paste the version > information from Help -> About? When I reported the bug, I had version 7.2.6.2 installed. I then upgraded to version 7.2.7.2 and the issue was still present. So both of these versions behave the same way. Other people have commented that the issues is present with other, more recent versions as well. Many thanks for everything you do!
The string from Help -> About contains further, possibly relevant information as well, please paste that from any of the versions you've encountered the issue with.
(In reply to Aron Budea from comment #15) > The string from Help -> About contains further, possibly relevant > information as well, please paste that from any of the versions you've > encountered the issue with. Apologies. I see what you mean now. Here is the pasted version info: Version: 7.2.7.2 (x64) / LibreOffice Community Build ID: 8d71d29d553c0f7dcbfa38fbfda25ee34cce99a2 CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Raster; VCL: win Locale: en-CA (en_CA); UI: en-US Calc: CL
Does it make a difference, if you deactivate Tools - Options - LibreOffice - OpenCL - Allow ...?
Yes it did make a difference. I deactivated OpenCL, restarted the application and did a hard recalculate, and it worked correctly. What are the implications/meaning of this discovery?
(In reply to slavaf2000 from comment #18) > Yes it did make a difference. I deactivated OpenCL, restarted the > application and did a hard recalculate, and it worked correctly. > > What are the implications/meaning of this discovery? Nice to hear. Maybe there is a bug in the OpenCL implementation. I'll put the earliest version back to 7.2.3 as perhaps Scott's result in comment 8 with 6.0 and no OpenCL is not relevant. You don't need OpenCL normally. More about OpenCL: https://en.wikipedia.org/wiki/OpenCL
With OpenCL disabled, the hard recalculate corrects all VLookups. Thank you Buovjaga, Could you please help on what the status should be updated to?
Scott and slavaf2000: see the instructions here on attaching some OpenCL related files to the bug report: https://wiki.documentfoundation.org/QA/FirstSteps#Computation-related_issues_in_Calc_.28_OpenCL_.29
Created attachment 181562 [details] opencl_profile.xml Version: 7.3.2.2 (x86) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
Created attachment 181563 [details] opencl_devices.log Version: 7.3.2.2 (x86) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
Created attachment 181564 [details] No "cache" folder in user profile I don't seem to have the folder "cache" in the profile. Please see screenshot.
Still rerpo in Version: 7.5.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 3e544b6938ee509a4f6df4c2e2996d71ce072506 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: ru-RU (ru_RU); UI: en-US Calc: CL threaded Lubos, while you near with OpenCL area could you please look at this problem too? Thank you
dholden committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/91ba7d22b0df33a3ca4102bc77869fe89921c34e tdf#149940 Fix vlookup result with OpenCL It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.