Created attachment 138843 [details] Example file to test the problem These spreadsheets were created with LibreOffice 5.4.3.2 and LibreOffice Dev. 6.0. The main problem here is the VLOOKUP function doesn't work correctly if we refer to an external XLS/XLSX file, and we get an Error (504) message in the cell. If we use the VLOOKUP function in the local (source.xlsx) file, it works perfectly. Steps to reproduce: 1. Create a new spreadsheet with LibreOffice 5.4 2. Click on Tools then choose Options. 3. In the Options set the LibreOffice Calc Formula options just like as you can see at the attached Formula_syntax_setting.PNG file. I use these settings because this is the only one which is interoperable with Microsoft Excel. 4. Fill at least two columns with some simple data. 5. Save the file as “Source.xlsx”. This will be the source file which contains the queried data. 6. Create a new spreadsheet with LibreOffice 5.4 and save as VLOOKUP_Target_First_Save.xlsx. (This will be the reference file. 7. In the VLOOKUP_Target_First_Save.xlsx file create a correct VLOOKUP function which refers to the “Source file” data. Here is the example code: VLOOKUP('file:///C:/Users/teszt_admin/Desktop/VLOOKUP_BUG/Source.xlsx'#$Sheet1.A1;'file:///C:/Users/teszt_admin/Desktop/VLOOKUP_BUG/Source.xlsx'#$Sheet1.A1:B3;2;0) 8. Click Ok in the Function Wizard and reload the file. Actual results: We get an Error (504) message in the cell for these two functions. Other functions work well if we use these steps, but the “Update links when opening” feature doesn't work, so we have to update the links manually. Expected results: We shouldn't receive an error code in the cell, because the function is correct and works perfectly in the local files. The Links should be updated when we answer “Yes” in the “This file contains links to another files Should they be updated?” dialog. Version: 5.4.3.2 Build ID: 92a7159f7e4af62137622921e809f8546db437e5 CPU threads: 4; OS: Windows 6.1; UI render: default; Locale: hu-HU (hu_HU); Calc: group Version: 6.0.0.0.beta1+ Build ID: 29228e83df009cf76ac819ed024527be1092f065 CPU threads: 4; OS: Windows 6.1; UI render: default; TinderBox: Win-x86@42, Branch:libreoffice-6-0, Time: 2017-12-04_23:15:34 Locale: hu-HU (hu_HU); Calc: group threaded
Created attachment 138844 [details] Example file containing VLOOKUP reference to the previous one
Created attachment 138845 [details] Screenshot of the error message
Created attachment 138846 [details] Formula syntax settings used to test the bug
Created attachment 138852 [details] Example file to test the problem - saved from LO 6
Created attachment 138853 [details] Example file containing VLOOKUP reference to the previous one - saved from LO 6
I confirm with Version: 5.3.8.0.0+ Build ID: 7f1297d9b4f449eb9ada8008fb21b7046d1a8f19 CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-11-10_15:56:34 Locale: nl-BE (en_US.UTF-8); Calc: group Version: 5.4.4.2 Build ID: 2524958677847fb3bb44820e40380acbe820f960 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Version: 6.1.0.0.alpha0+ Build ID: 15d9087ff1e495e234b2cf073329c3358abb4dc0 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group threaded
confirmed in comment 6
Created attachment 139563 [details] SUMIF and VLOOKUP comparison - SUMIF is working perfectly fine
Created attachment 139616 [details] Screenshot of the LibreOFfice with the fix With big help of Eike, I managed to fix that issue. Review: https://gerrit.libreoffice.org/#/c/49263/ In attachment you could find the screenshot from LO after fix.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5c3d0a70a0edb182714725ef920d74667feffb0e tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP It will be available in 6.1.0. 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.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b8e9f185c509213b4daae018ec27483dacdad2a0&h=libreoffice-6-0 tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP It will be available in 6.0.1. 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.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e392f847bff0321d7884daabe74d91d90f07a0bf&h=libreoffice-5-4 tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP It will be available in 5.4.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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/3b30ea9c00598b9de85333b67e8b3339d414eec3 tdf#114820: sc_uicalc: Add unittest It will be available in 7.4.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.