Description: MATCH function ignores the last value in an inline array. Steps to Reproduce: 1. Open new Calc. 2. Insert formula "=MATCH(2; {1,2}; 1)" to Cell [A1]. Actual Results: 3. Result is 1. This is an incorrect value. Expected Results: 3. Result is 2. Reproducible: Always User Profile Reset: No Additional Info: This bug does not depend on the direction of the inline array. If the argument specifies an exact match, "= MATCH (2; {1,2}; 0)" returns the correct value "2". For example, adding the values of an inline array to "= MATCH (2; {1,2,3}; 1)" gives the correct answer "2". However, if you change the first argument to "3" and change the formula to "= MATCH (3; {1,2,3}; 1)", the result is still "2" and does not return the correct answer "3". And similarly for descending order, "= MATCH (1, {2,1}, -1)" returns "1", but should return "2". Also, each of the extreme examples of this bug rule =MATCH(1;{1};1) =MATCH(1;{1};0) =MATCH(1;{1};-1) returns #N/A 1 #N/A , but they should all return "1". The following formulas all return the wrong value "1" in Version 24.8.0.0. alpha1 and later, but all return the correct answer "2" in Google Spreadsheet and MS Excel Web Version. =MATCH(2; {1,2}; 1) =MATCH(1; {2,1}; -1) =MATCH("2"; {"1","2"}; 1) =MATCH("1"; {"2","1"}; -1) --- These formulas worked correctly until version: 24.2.7.2. Not reproduced with Version: 24.2.7.2 (X86_64) / LibreOffice Community Build ID: ee3885777aa7032db5a9b65deec9457448a91162 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: en-US (ja_JP); UI: en-US Calc: default --- Reproducible with Version: 24.8.0.0.alpha1 (X86_64) / LibreOffice Community Build ID: a17e39caaf73108bee692d6f64a44c62f4066f1d CPU threads: 4; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Vulkan; VCL: win Locale: en-US (ja_JP); UI: en-US Calc: CL threaded Reproducible with [2025-06-20] Version: 26.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 024053c0c140e1d167a0276f1885a21a160b45ac CPU threads: 4; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster; VCL: win Locale: en-US (ja_JP); UI: en-US Calc: CL threaded
bibisected with linux-64-24.8 author Winfried Donkers commit f7039822c7ad3987326e1c20ea4a745c158f9682 tdf#127293 Add Excel2021 function XLOOKUP to Calc
Hi nobu, Are you sure '2' is the expected behaviour? I checked with =MATCH(2; {1,2}; 1) before f7039822c7ad3987326e1c20ea4a745c158f9682 and it returns 1 ( Same with Excel ). After f7039822c7ad3987326e1c20ea4a745c158f9682, it returns #N/A
The relevant method is ScMatch in interpr1.cxx When a cell reference is given as parameter, then the members nCol1, nCol2, nRow1 and nRow2 of variable vsa are determined by the method PopDoubleRef. When the lookup vector is given by an inline array, then nRow2 and nCol2 have to be set directly. The needed values are available from the size of the matrix. The error is similar to that of LOOKUP in bug 167134. That was fixed, and the fix there can be used to get an idea how to fix this bug.
Created attachment 201646 [details] Test cases for match mode 1
Using an inline array with the MATCH function is not a rare use. At least in my country, inline arrays are introduced as examples of EXCEL functions. This bug returns the wrong answer, unlike an error or crash. Saburo realized the importance and quickly identified the problem. Maybe he couldn't do more because he didn't have the authority to change the importance. Those with the authority to change the level of importance decided that the issue was not so important.
(In reply to Regina Henschel from comment #4) > Created attachment 201646 [details] > Test cases for match mode 1 Hi Regina, Thanks a lot for the test cases, I have added it to the unit test file: https://gerrit.libreoffice.org/c/core/+/187492
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/aa4b18b720746038206152689ef31231d474b8a4 tdf#167171 - fix MATCH function wrong result with inline arrays It will be available in 26.2.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.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-25-8": https://git.libreoffice.org/core/commit/411820caff6cbf01aa0affc01c11eefed02b9f98 tdf#167171 - fix MATCH function wrong result with inline arrays It will be available in 25.8.0.2. 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.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-25-2": https://git.libreoffice.org/core/commit/711ff1b95af0ea2f60f48b3bb4e78b9175d3d732 tdf#167171 - fix MATCH function wrong result with inline arrays It will be available in 25.2.6. 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.
I have tested match mode 1 and match mode -1. They are OK in a debug build that includes the fix. Version: 26.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 0ab2da12fb6ea515339b113f622720b11c65afca CPU threads: 32; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded There is a problem with match mode 0 and wildcards. I have written bug 167439 for that.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-25-2-5": https://git.libreoffice.org/core/commit/9e051f75d5ab01d8255734f468e0e63ed6776852 tdf#167171 - fix MATCH function wrong result with inline arrays It will be available in 25.2.5. 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.