Bug 120752

Summary: MATCH() shows inconsistent behavior when used improperly, could use better documentation
Product: LibreOffice Reporter: pawo2500
Component: CalcAssignee: Olivier Hallot <olivier.hallot>
Status: RESOLVED FIXED    
Severity: minor CC: oliver.brinzing
Priority: medium    
Version: unspecified   
Hardware: All   
OS: All   
Whiteboard: target:6.3.0
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 80430    
Attachments: Screencast of LO Calc, showing the current behavior
Screenshot of current Function Wizard entry

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.