Bug 72348 - Other: Match function doesn't work
Summary: Other: Match function doesn't work
Status: RESOLVED DUPLICATE of bug 74558
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: Other Linux (All)
: low minor
Assignee: Not Assigned
Whiteboard: BSA target:4.3.0
Keywords: possibleRegression
Depends on:
Reported: 2013-12-05 12:24 UTC by pete_herworth
Modified: 2015-12-15 22:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

match.ods (9.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-08 19:38 UTC, pierre-yves samyn

Note You need to log in before you can comment on or make changes to this bug.
Description pete_herworth 2013-12-05 12:24:07 UTC
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
Last worked in: release
Comment 1 GerardF 2013-12-05 12:48:47 UTC
What formula did you enter?
Please provide a spreadsheet test with the error.
Comment 2 pete_herworth 2013-12-05 13:53:35 UTC
Sorry, I should have done more testing.
The formula was working in LO 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.
Comment 3 pierre-yves samyn 2013-12-08 19:38:08 UTC

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 :

Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a


Build ID: 1a27be92e320f97c20d581a69ef1c8b99ea9885d

Steps to reproduce:

1. Open the match.ods attached in
B1 displays 6 

2. Open the match.ods attached in
B1 display #N/A even with 3rd parameter type = 1 (search array is not sorted.)

Comment 4 pierre-yves samyn 2013-12-08 19:38:35 UTC
Created attachment 90480 [details]
Comment 5 GerardF 2013-12-08 20:17:24 UTC
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.
Comment 6 pete_herworth 2013-12-09 09:32:14 UTC
Perhaps I should have added that my cells are always sorted in ascending order so the "type" parameter is unnecessary for my sheet.
Comment 7 Commit Notification 2014-02-14 01:33:24 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":


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:
Affected users are encouraged to test the fix and report feedback.
Comment 8 Kohei Yoshida 2014-02-14 01:39:30 UTC
This one shares the same root cause with Bug 74558.

*** This bug has been marked as a duplicate of bug 74558 ***
Comment 9 Robinson Tryon (qubit) 2015-12-15 22:09:34 UTC
Migrating Whiteboard tags to Keywords: (PossibleRegression)