Created attachment 74289 [details] ODS that shows the bug as described in "Steps to reproduce" Problem description: MATCH() function fails (err: 504) when parameter 2 (LookupArray) is a single-cell range Steps to reproduce: 1. Create a new workbook 2. Create a named range (CTRL+F3) with * name = "range1" * range = "$A$1" * [other parameters: any] 3. In cell A1, insert character: a 4. In cell B1, insert formula: =MATCH("a";range1) Current behavior: Cell B1 shows "Err:504" Expected behavior: Cell B1 shows "1" Additional information: 5. Create a named range (CTRL+F3) with * name = "range2" * range = "$A$1:$A$1" * [other parameters: any] 6. In cell B2, insert formula "=MATCH("a";range2)" => displays "1", as expected It might be worth noting that: MATCH("a";A1) => Err:504 (expected: 1) MATCH("a";A1:A1) => 1 (as expected) Same behaviour seen on: - v4.0.0.3 (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89 - v3.6.5.2 (Build ID: 5b93205) - v3.5.4.2 (Version ID 350m1(Build:2)) Operating System: Ubuntu Version: 3.6.5.2 release
Taking this.
Also, in the same test scenario, insert the following formula to (e.g.) cell B5: =EQUIV("a";INDIRECT("range2")) => Err:504 Although: =EQUIV("a";range2) => "1", as expected (or is this a problem with INDIRECT() ?)
What is the EQUIV() function? It's not part of Calc core. =INDIRECT("range2") in the above scenario works.
Sorry, replace "EQUIV" by "MATCH" in my previous comment. (EQUIV is the French version of MATCH).
Ah, I should had noticed.. I'll take a look into that as well.
Effectively both the same cause, a single cell reference not being accepted.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c0fdab890086c2cb749c348db42eb760e31539de resolved fdo#60366 make MATCH() accept a single cell reference 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.
Pending review for 4-0 as https://gerrit.libreoffice.org/2018 and for 3-6 as https://gerrit.libreoffice.org/2019
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-3-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b4c78b8480b1cc1e867180fc994f5c5162fa3e42&h=libreoffice-3-6 resolved fdo#60366 make MATCH() accept a single cell reference It will be available in LibreOffice 3.6.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e577ebc9403d77fe4847a2e1d55a83d432f214f8&h=libreoffice-4-0 resolved fdo#60366 make MATCH() accept a single cell reference It will be available in LibreOffice 4.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.