Bug 120752 - MATCH() shows inconsistent behavior when used improperly, could use better documentation
Summary: MATCH() shows inconsistent behavior when used improperly, could use better do...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium minor
Assignee: Olivier Hallot
URL:
Whiteboard: target:6.3.0
Keywords:
Depends on:
Blocks: HelpGaps-NewFeatures
  Show dependency treegraph
 
Reported: 2018-10-21 13:34 UTC by pawo2500
Modified: 2019-02-19 19:07 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screencast of LO Calc, showing the current behavior (3.00 MB, video/x-flv)
2018-10-21 13:34 UTC, pawo2500
Details
Screenshot of current Function Wizard entry (44.74 KB, image/png)
2018-10-21 15:19 UTC, pawo2500
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pawo2500 2018-10-21 13:34:16 UTC
Created attachment 145878 [details]
Screencast of LO Calc, showing the current behavior

MATCH() takes 3 parameters, the last (Type) being optional. Built-in description only says Type can be -1, 0 or 1, without explaining what any of those modes do, and without informing that 1 is the default (and it usually is NOT the mode user wants to use, but it's been default since forever). The behavior of this function is described here: https://help.libreoffice.org/Calc/Spreadsheet_Functions#MATCH . IMHO this description should be copied into Calc.

What is also worrying is that on small datasets, MATCH() works "properly" (Type=0) independently of what Type is actually set to. See attached screencast. It is quite confusing, but we may need to leave it as-is to preserve compatibility.
Comment 1 Oliver Brinzing 2018-10-21 14:05:49 UTC
"Function Wizard" dialog has a [Help] button.

select MATCH and press [Help] will open LibreOffice Help:

https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060109.html?System=WIN&DbPAR=CALC&HID=SC_HID_FUNC_VERGLEICH#bm_id3154210
Comment 2 pawo2500 2018-10-21 15:19:15 UTC
Created attachment 145879 [details]
Screenshot of current Function Wizard entry
Comment 3 pawo2500 2018-10-21 15:20:44 UTC
(In reply to Oliver Brinzing from comment #1)
> "Function Wizard" dialog has a [Help] button.
> 
> select MATCH and press [Help] will open LibreOffice Help:
> 
> https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060109.
> html?System=WIN&DbPAR=CALC&HID=SC_HID_FUNC_VERGLEICH#bm_id3154210

Clicking [Help] opens up Help on entry ERROR.TYPE, searching for MATCH indeed works. (I reverted to 6.0.6.2 meanwhile, maybe that's why). Still, it would be nice to have it directly in Function Wizard (Quality of Life improvement) (see Screenshot of current Function Wizard entry).
Comment 4 Commit Notification 2019-02-19 19:04:50 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/87d46dc80a607ea66e52c3dc81dea20fd4fe07f8%5E%21

tdf#120752 Clarify MATCH() third argument 'Type'

It will be available in 6.3.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.