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.
I can confirm the bug in Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0) on Ubuntu 12.04 (x86)
Reproducible with LO 4.3.2.2 (Win 8.1)
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-12-20
Still present as originally described in Version: 5.0.3.2 Build ID: e5f16313668ac592c1bfb310f4390624e3dbfb75 Locale: en-GB (en_GB) Windows 10.1
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.6 or 5.2.3 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170103
Still present as originally described in Version: 5.2.4.2 Build ID: 3d5603e1122f0f102b62521720ab13a38a4e0eb0 Windows 10
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
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
typo: empty string => empty cells
Wait, Excel returned #N/A too at least for =MATCH("",A1:A2,0)
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.
Dear David Lynch, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
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
Created attachment 156170 [details] match demo
@erack: you might be interested in this
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.
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 "
(In reply to David Lynch from comment #17) As you mentioned, the specification defines the Search argument as 'Scalar', which is defined in ODF 1.3 sect. 4.11.2 'Scalar' (there's the same definition in ODF 1.2, so the spec version doesn't matter in this case): > A Scalar value is a value that has a single value. A reference to more than one > cell is not a scalar (by itself), and is converted to one as described below. An > array with more than one element is not a scalar. The types Number (including a > complex number), Logical, and Text are scalars. Further, there is ODF 1.3 sect. 4.7 'Empty Cell': > An empty cell is neither zero nor the empty string, and an empty cell can be > distinguished from cells containing values (including zero and the empty string). > An empty cell is not the same as an Error, in particular, it is distinguishable > from the Error #N/A (not available). No, there is no need in MATCH to define any kind of "exception allowed for empty cells", because the standard already defines that empty cell is not the same as the scalars you can put into the function's 'Search' argument. Neither "" nor 0 (nor any regex) may match an empty cell. No, this in not a bug, nor should it change.