Bug 164004 - XLOOKUP across files does not work correctly
Summary: XLOOKUP across files does not work correctly
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
24.8.3.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: implementationError
Depends on:
Blocks: Cell-Formula Calc-External-Datalink
  Show dependency tree / graph
 
Reported: 2024-11-23 01:21 UTC by antsyUzr
Modified: 2025-01-17 01:13 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Attachments
Sample files as described in the bug report: Fr.ods (9.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2024-11-23 01:22 UTC, antsyUzr 		Details
Sample files as described in the bug report: To.ods (11.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2024-11-23 01:23 UTC, antsyUzr 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description antsyUzr 2024-11-23 01:21:14 UTC 
Description:
XLOOKUP across files does not work correctly. 
XLOOKUP within the same ods file works correctly.
VLOOKUP works fine.

Steps to Reproduce:
1.write "tiger" in cell B2 and "woods" in cell C2
2.write "woods" in cell B3 and "tiger" in cell C3
3.save sheet as "fR" and file as "Fr.ods"
4.start new file To.ods, sheet "To"
5.in cell B2, write "tiger"
6.in cell C2, enter formula =XLOOKUP(B2,'file:///tmp/Fr.ods'#$fR.B$1:B$1048576,'file:///tmp/Fr.ods'#$fR.C$1:C$1048576)

Expected Results:
woods


Reproducible: Always


User Profile Reset: No

Additional Info:
* XLOOKUP of string not in remote file results in "#N/A" as expected
* VLOOKUP works correctly, results in "woods"
=VLOOKUP(B2,'file:///tmp/Fr.ods'#$fR.B$1:C$1048576,2,0)
* XLOOKUP within the same ods file works correctly, results in "woods" if you move sheet [Fr] into same file 'To.ods' as where the XLOOKUP is running
Comment 1 antsyUzr 2024-11-23 01:22:48 UTC 
Created attachment 197734 [details]
Sample files as described in the bug report: Fr.ods
Comment 2 antsyUzr 2024-11-23 01:23:05 UTC 
Created attachment 197735 [details]
Sample files as described in the bug report: To.ods
Comment 3 antsyUzr 2024-11-23 01:24:34 UTC 
software: LibreOffice-24.8.3.2.basic-x86_64.AppImage
source  : https://appimages.libreitalia.org/portable/LibreOffice-24.8.3.2.basic-x86_64.AppImage
Comment 4 antsyUzr 2024-11-23 01:28:42 UTC 
Expected Results:
"woods"

Actual Results:
"0"

Reproducible: Always
Comment 5 Eric Rodriguez 2024-11-23 18:17:12 UTC 
Thank you for reporting the bug. I can confirm that the bug is present in
Version: 24.8.3.2 (X86_64) / LibreOffice Community
Build ID: 48a6bac9e7e268aeb4c3483fcf825c94556d9f92
CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

Thank you for reporting the bug. I can confirm that the bug is present in
Version: 25.2.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: 869fff459cbee6a2edadd5c24f2b65c199712de6
CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded
Comment 6 Buovjaga 2024-11-23 19:10:52 UTC 
As the function is new in 24.8, let's assume implementationError
Comment 7 m_a_riosv 2024-11-23 22:22:29 UTC 
Maybe the same issue as in tdf#89013 "Regular expressions doesn't work with MATCH and VLOOKUP when searched range is in other file."