Problem description: Steps to reproduce: 1. create formula in cell using MATCH().... 2. .... 3. .... Current behavior: cell shows N/A Expected behavior:should return row no of matched cell I also tried OFFSET() and got same response. Have not tested anymore and only use MATCH() Operating System: openSUSE Version: 4.2.0.0.beta1 Last worked in: 4.1.3.2 release
What formula did you enter? Please provide a spreadsheet test with the error.
Sorry, I should have done more testing. The formula was working in LO 4.1.3.2 as it stood but isn't working in 4.2 because the first 4 rows are blank. The original formula was this =MATCH(TODAY(),A1:A2000) and returns the cell ref for the last cell in the range that has today's date. If I change it in LO 4.2 to: =MATCH(TODAY(),A5:A2000)+4 - it works as I want it. I don't know if you would still consider this a bug.
Hello You omitted the third parameter of the formula: "type" as help says: "If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order" See https://help.libreoffice.org/Calc/Spreadsheet_Functions#MATCH Anyway I see indeed a difference between : Version: 4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a and Version: 4.2.0.0.beta2 Build ID: 1a27be92e320f97c20d581a69ef1c8b99ea9885d Steps to reproduce: 1. Open the match.ods attached in 4.1.3.2 B1 displays 6 2. Open the match.ods attached in 4.2.0.0.beta2 B1 display #N/A even with 3rd parameter type = 1 (search array is not sorted.) Regards Pierre-Yves
Created attachment 90480 [details] match.ods
I see another difference : With your attachment 90480 [details] , sort in ascendant order the dates only (A5:A9) leaving blank cells before and after. 4.1.3 show 7 4.2.0 show #N/A It seems that 4.1.3 gives the result if data are sorted even with leading blank cells.
Perhaps I should have added that my cells are always sorted in ascending order so the "type" parameter is unnecessary for my sheet.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fffc6971783be55dff51e07f1d5e33866f607197 fdo#72348: Add test to catch this. 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.
This one shares the same root cause with Bug 74558. *** This bug has been marked as a duplicate of bug 74558 ***
Migrating Whiteboard tags to Keywords: (PossibleRegression) [NinjaEdit]