I am using the Match-Exact combination to search for the location of a character in a column, but when a character is a number (regardless of whether the cell is configured as text or number), I get #N/A. I have been using this function on OpenOffice without any problem, but now I am trying to move to LibreOffice, but for some reason, I am getting this error.
I have a column with numbers and characters including symbols ordered in a random way, and trying to find in which row my character is. The formula I am using is:
Match(1,Exact(column range,cell containing the character to search),0)
Thank you for any help.
Check the attached example, and report your observations.
Concerning supposed issues or bugs: Always name the used version and the OS.
Best (always) attach an example file showing the issues clearly for you.
My example was made with LibO V 7.2.3.2 under (updated) Win 10.
disask71688usingEXACTwithMATCH.ods (14.1 KB)
If you want to talk of a single character “1”, please don’t call it a number but a digit. Otherwise misunderstandings are to be expected. We shouldn’t use words to the effect that “1234 is a number consisting of four numbers.” need to be accepted.
(Yes. I know that education should avoid such issues, but doesn’t.do its duty.)
The specification of the function MATCH is containing the constraint
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.
EXACT() returns logical values, and should thus be excluded as the proliferator of values in the SearchRegion by the constraint taken the strict way. However, LibO always made use of the permitting clause.
Concerning the arguments separator (comma or semicolon) you may consider to also read what I wrote in my answer here, and probably to regard it. Support global cooperation.
Lupp, thank you for your response. I am not surprised about the results of your example. As by my comment to Villeroy everything works when I precede a digit (as per your lexicon) with a " ’ ", which is terms of Calc means the next digit is actually text. I understand how difficult is to understand each other when coming from different branches of engineering.
Therefore I guess, as you suggested, that I should provide basic information plus a more concrete example.
I am using WIndows 10 last released update (a couple of days ago), and LO 7.2.4.1 (x64).
Last finding, which is acceptable for me. If in my range I precede each digit with " ’ ", the MATCH-Exact works fine.
In the attached example, numbers (sorry, digits) 3 and 4 in the H column are defined as Text, while the other 8 digits are defined as Numbers (Calc terminology), and those generate a #N/A, which did not happen in OO.
Sorry, I don’t know how to send the attachment.
Start a new posting and click the upload button (7th from the left) above the input box.
t71688_1.ods (13.0 KB)
Hmmm. I obviously had missed this. Sorry.
In short:
There seems to be a bug. EXACT() is working differently depending on whether it is called for
- single references or scalar expressions or
- by a formula forcing it under array-evaluation explicitly or due to specifications (as is the case using
MATCH()).
I can confirm this based on another test.
(In fact EXACT() isn’t made for comparing numbers, but texts - and it is specified this way. Mixed usage requires automatic conversion based on a more general specification for Calc - and causing problems also in different places).
6.20.8
...
Syntax: EXACT( Text T1 ; Text T2 )
...
6.3 Implicit Conversion Operators
6.3.1 General
Any given function or operand takes 0 or more parameters, and each of those parameters has an expected type. The expected type can be one of the base types, identified above. It can also be of some conversion type that controls conversion, e.g., “Any” means that no conversion is done (it can be of any type); NumberSequence causes a conversion to an ordered sequence of zero or more numbers. If the passed-in type does not match the expected type, an attempt is made to automatically convert the value to the expected type. An Error is returned if the type cannot be converted (this can never happen if the expected type is Any). Unless otherwise noted, any conversion operation applied to a value of type Error returns the same value.
…
6.3.14 Conversion to Text
If the expected type is Text, then if value is of type:
• Number, transform into Text (with no whitespace).
• Text, return it.
• Logical, return “TRUE” if it is true and “FALSE” if it is false.
• Reference: perform conversion to scalar. If the referenced cell is empty, treat as an empty string (a text value with length 0). Then perform as above.
There is also subchapter 6.3.4 concerning ““Force to array context (ForceArray)””. There may occur conflicts.
Try (formula array):
=MATCH(1; EXACT("abcd"; A1:A3);0)
Agree, Lupp. This functionality works fine in OO, and I was expecting would be the same in LO.
I will either keep working with this with OO or inserting a " ’ " in a couple of hundred places where I have a digit (number) in the columns.
It would be nice if this issue is corrected in a future rev.
Thank you very much for your help.
Villeroy, after Lupp’s comments, its clear that the function does not support a search for digits, therefore, is of no use to send an example of my problem, unless you think otherwise.
The problem with numbers can be solved like this:
=MATCH(1; EXACT(F1 & "*"; A1:A3 & "*");0)
You hit the nail sokol92. Can you give me a lead to read about the & “*” functionality? I would like to know what effect that term has on the digit I am searching for.
Thank you for your help.
I chose the bad character: “*”.
It is better to use construction:
=MATCH(1; EXACT(F1 & ""; A1:A3 & "");0)
Concatenation with any text converts the entire expression to text (including numbers).
Which would be?
EXACT() behaves like any other function that expects a single scalar argument when forced into array mode.
Excellent!! Thank you very much!!
Case closed.
It behaves differently under array-evaluation. I’ll prepare a report.
This is definitely just a workaround. Regarding its specification EXACT() must do the conversion automatically even under array-evaluation.
BTW: Why don’t you compare simply using the comparation operator =? Anyway you should always enable the option Options>LibreOffice Calc>Calculate>Case-sensitive. We shouldn’t surrender the meaning of = to a queer option. Relieving a comparison for = to work case-insensitive can be done by UPPER(firstArgument)=UPPER(secondArgument). This doesn’t require a special function of somehow obscure functionality. (“Converts both sides to Text, and …” which doesn’t meet my concept of EXACT comparison.)
That is good lead Lupp. I’ll check what I have there. I certainly did not change anything, but I will check that out. And I agree the & " " is a workaround, but I leave it to the developers to fix the issue staying open to help in anything I can.
The number 1 is not equivalent to the text “1”.
MATCH(“1”;range;0)
The EXACT function does not apply here because there is no distinction between upper-case and lower case “1”.
Villeroy, thank you for your attention.
Your assumption is not right. The Exact function has to be used because I need to point out the right exact letter, upper or lower case. I never said (or I should say, omit to say) that the range includes upper and lower case, besides numbers and symbols (like &,%,#, etc). Anyways, I found, based on your response that if I enter '4 as a reference and '4 in the column, then the Match-Exact function works. Therefore, I think there is something wrong in the cell definition, or otherwise a difference in how OpenOffice Calc and LibreOffice treat the information. I will investigate that tomorrow Eastern US time.
Will enter here what I find in the process.