Bug 54255 - MATCH does not find empty cells
Summary: MATCH does not find empty cells
Status: REOPENED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2012-08-30 09:04 UTC by David Lynch
Modified: 2019-11-29 20:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
match demo (12.55 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-28 18:29 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Lynch 2012-08-30 09:04:43 UTC
enter "A" in A1

=match("",A1:A2,0)    [1]
=match("^$",A1:A2,0)  [2]
=match(0,A1:A2,0)     [3]

all give the answer #N/A

In regular expression documentation, it says
"^$  Finds an empty paragraph."
 which indicates that [2] should return 2


In http://help.libreoffice.org/Calc/Handling_of_Empty_Cells, it indicates that either [1] or [3] should return 2.
Comment 1 Sören 2012-12-27 13:23:59 UTC
I can confirm the bug in
Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0)
on Ubuntu 12.04 (x86)
Comment 2 A (Andy) 2014-10-18 19:00:13 UTC
Reproducible with LO 4.3.2.2 (Win 8.1)
Comment 3 QA Administrators 2015-12-20 16:06:34 UTC Comment hidden (obsolete)
Comment 4 David Lynch 2015-12-20 18:10:20 UTC
Still present as originally described in
Version: 5.0.3.2
Build ID: e5f16313668ac592c1bfb310f4390624e3dbfb75
Locale: en-GB (en_GB)
Windows 10.1
Comment 5 QA Administrators 2017-01-03 19:46:12 UTC Comment hidden (obsolete)
Comment 6 David Lynch 2017-01-06 10:43:38 UTC
Still present as originally described in
Version: 5.2.4.2
Build ID: 3d5603e1122f0f102b62521720ab13a38a4e0eb0
Windows 10
Comment 7 QA Administrators 2018-07-02 02:35:33 UTC Comment hidden (obsolete)
Comment 8 himajin100000 2018-07-02 04:16:19 UTC
the only line that changes mbMatchEmpty is...

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/queryparam.cxx?r=db808563#295

FillInExcelSyntax is called by these functions.

https://opengrok.libreoffice.org/s?refs=FillInExcelSyntax&project=core

so matching empty string is currently available only in

SumIf
AverageIf
CountIf
SumIfs
AverageIfs
CountIfs
MinIfs
MaxIfs

Where IterateParametersIf or IterateParametersIfs is called or in DB-related functions, and it does not work with Match.

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=ceabe204#4667
Comment 9 himajin100000 2018-07-02 04:27:12 UTC
typo:
empty string => empty cells
Comment 10 himajin100000 2018-07-02 08:06:53 UTC
Wait, Excel returned #N/A too at least for =MATCH("",A1:A2,0)
Comment 11 himajin100000 2018-07-02 08:42:50 UTC
And what I can tell from the code,

=COUNTIF(B1:B5,"^$") returns 0, when regular expression is enable in option and all the 5 cells in B1:B5 is empty.

However,
=COUNTIF(B1:B5,"") is 5

minor note:
honestly speaking, as of 2018-07-02T08:37(UTC) ,
I don't think I can find the INDICATED description on how MATCH function should work for the empty cells. just VLOOKUP. I can agree that the document  SUGGESTS/IMPLIES the behavior.
Comment 12 QA Administrators 2019-11-28 04:08:33 UTC Comment hidden (obsolete)
Comment 13 David Lynch 2019-11-28 16:23:32 UTC
Still present as originally described in
Version: 6.3.3.2 (x64)
Build ID: a64200df03143b798afd1ec74a12ab50359878ed
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: en-GB (en_GB); UI-Language: en-GB
Calc: threaded
Comment 14 Oliver Brinzing 2019-11-28 18:29:57 UTC
Created attachment 156170 [details]
match demo
Comment 15 Oliver Brinzing 2019-11-28 18:38:07 UTC
@erack: you might be interested in this
Comment 16 Eike Rathke 2019-11-29 10:46:48 UTC
All three forms in comment 0 original description do *not* search for an empty cell without content.

  =match("",A1:A2,0)    [1]

searches for content of an empty string.

  =match("^$",A1:A2,0)  [2]

searches for content with an empty paragraph.

  =match(0,A1:A2,0)     [3]

searches for content of numeric 0.

There is no way to find an empty cell with MATCH() or VLOOKUP() or HLOOKUP().
Also, the mentioned help page does not indicate this would be possible. It describes the handling of empty cells *returned* by VLOOKUP() and in use with comparison operators.
The search Criterion mentioned in comment 8 as used for the COUNTIF() family of functions is a different syntax and completely unrelated.

The behaviour will not be changed.
Comment 17 David Lynch 2019-11-29 20:13:27 UTC
I agree that the help page does not indicate that it would be possible to find an empty cell. But, I think that the current behaviour of MATCH (etc) is incompatible with Open Formula (version 1.2) and so should be considered a bug. Nowhere in the specification is an exception allowed for empty cells:
"
6.14.9MATCH

Summary: Finds a Search item in a sequence, and returns its position (starting from 1).

Syntax: MATCH( Scalar Search ; Reference|Array SearchRegion [ ; Integer MatchType = 1 ] )

Returns: Any

Constraints: -1 <= MatchType <= 1; The searched portion of SearchRegion shall not include Logical values. Evaluators may evaluate expressions that do not meet the constraint that the searched portion of a SearchRegion not include Logical values.

SearchRegion shall be a vector (a single row or column)

Semantics:

    ●MatchType = -1 finds the smallest value that is greater than or equal to Search in a SearchRegion where values are sorted in descending order. From a sequence of identical values >= Search the last value is taken. If no value >= Search exists, the #N/A Error is returned. If Search is of type Number and the value found is of type Text, the #N/A Error is returned. 

    ●MatchType = 0 finds the first value that is equal to Search. Values in SearchRegion do not need to be sorted. If no value equal to Search exists, the #N/A Error is returned. 

    ●MatchType = 1 or omitted finds the largest value that is less than or equal to Search in a SearchRegion where values are sorted in ascending order. From a sequence of identical values <= Search the last value is taken. If no value <= Search exists, the #N/A Error is returned. If Search is of type Text and the value found is of type Number, the #N/A Error is returned. 

If a match is found, MATCH returns the relative position (starting from 1). For Text the comparison is case-insensitive. MatchType determines the type of search; if MatchType is 0, the SearchRegion shall be considered unsorted, and the first match is returned. If MatchType is 1, the SearchRegion may be assumed to be sorted in ascending order, with smaller Numbers before larger ones, smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True. If the types are mixed, Numbers are sorted before Text, and Text before Logicals; evaluators without a separate Logical type may include a Logical as a Number. If MatchType is -1, then SearchRegion may be assumed to be sorted in descending order (the opposite of the above). If MatchType is 1 or -1, evaluators may use binary search or other techniques so that they do not need to examine every value in linear order. MatchType defaults to 1.

The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. 3.4
"