Description: Combination of various nested MATCH, INDIRECT, ADDRESS functions behaves inpredictably, whilst working correctly when splitting the formula over two cells. Only malbehaves in LibreOffice and under various OS'ses. Behaves *correctly* in MS-Office using xslx exports, and also behaves *correctly* in OpenOffice using the original .ods file. Steps to Reproduce: I did prepare a sheet showing the problem and a correct working workaround using two cells with split functionality (URL: https://www.mpxf.nl/data/bugs/LibreOffice%205.3.2.2%20Bug.zip) Basically the looks up the two occurences (assumed to be present) in a list named RoleAssignment for a given entry in a table named Role. Occ#1 is lookedup using MATCH(), whereas Occ#2 is also searched for using MATCH, but starting just after Occ#1 by restricting the address range within the table RoleAssignment formula (bad behaving): =MATCH($E4;INDIRECT(ADDRESS(ROW(RoleAssignment)+$G4;COLUMN(RoleAssignment))&":"&ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;COLUMN(RoleAssignment)));0)+$G4 split formula (well behaving): (1st step) =ADDRESS(ROW(RoleAssignment)+G4;COLUMN(RoleAssignment)) & ":" & ADDRESS(ROW(RoleAssignment)+ROWS(RoleAssignment)-1;COLUMN(RoleAssignment)) (2nd step) =MATCH($E4;INDIRECT($I4);0)+$G4 Actual Results: #N/A #N/A 8 8 6 Expected Results: 3 6 10 8 9 Reproducible: Always User Profile Reset: No, but I used various computers and OS'ses (WINXP-SP3, WIN10, Linux SUSE Leap 42.2) Additional Info: Note the link above (under "Steps to reproduce") to the prepared test spreadsheet showing the malbehaviour and a screenshot of the result on my systems User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0
Created attachment 133191 [details] Test spreadsheet showing the bug
Created attachment 133192 [details] Screenshot showing result
Already in 5.1.5 at least, don't have earlier versions at hand. Investigating.
The problem is that for MATCH (and LOOKUP, HLLOKUP and VLOOKUP) the second parameter forces its argument and sub-arguments to Array if not of type Reference, which happens during compile time, but INDIRECT during runtime returns Reference so should not be treated that way. Here arguments to INDIRECT (namely ADDRESS and ROW and COLUMN) are forced into Array context hence make INDIRECT return different ranges than expected.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8ba4679c96e08eebf58347edfba33bb9610701b5 Move ScParameterClassification::Type to formula::ParamClass, tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=031bf555256f8f827b4f32124f88c4012d28dcd3 Disambiguate Reference -> uno::Reference (tdf#107724 prep rel) It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f39e1b1079b04c8d14d3fa4a00d2cf519dba05a5 Change IsForceArrayParameter() to GetForceArrayParameter(), tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=47023116d6bf89bb2e0b8b5293272f63db32fbc2 Disambiguate Reference -> uno::Reference (tdf#107724 prep rel) It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1cf44947161d8fff6e3edb3f7a1dd01c81963b42 Hold ParamClass at FormulaByteToken instead of bool, tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=656cf4d2e7c7d193d4d23900a344d6625fc217ac Add ParamClass ScParameterClassification::CommonData::eReturn, tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e52beea63ddd7087c61df5838f9643d68486d71e GetParameterType: SAL_MAX_UINT16 for return ParamClass, tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3e67f82fcee0ef43ad8fd9f36e0d618c33d4ee72 inherit ReferenceOrForceArray only if nested not Reference, tdf#107724 prep It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=eae5503d1b3a7f9aace2603d6ce91003ede33974 Resolves: tdf#107724 INDIRECT and OFFSET return ParamClass::Reference It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6dc2efcbd68b9cdad9375eee3ad45b4bd2893733 ParamClass::Reference return for range creating OpCodes, tdf#107724 related It will be available in 5.4.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c8c8e70be7ec0d47e13724105eccbf4b9ab5a7c6 Add unit test for non-propagation of array context, tdf#107724 It will be available in 5.4.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.