Where and how can I configure the functions ‘=LOOKUP()’ and ‘=MATCH()’ to recognize and distinguish between uppercase and lowercase letters, like ‘=FIND()’ and ‘=REGEX()’ do?
LO v.25.2.1.2
MATCH is documented case-insensitive in ODF (curiously, LOOKUP documentation lacks similar clause, but it is actually identical; @regina do you know by chance, if omission of explicit “case insensitive” clause there is intentional?).
But given that they both allow regexes - you can use it (and its flags) since version 7.0 (tdf#78840). Using a regex like "(?-i)\Q" & "search string" & "\E" would do the trick, activating case sensitivity (or, rather, deactivating the default case-insensitivity), and disabling treatment of regex metacharacters between \Q … \E (in the hope that your data doesn’t include \E itself 
)
2 Likes
It should explicitly recognize automatically whether the letter “m/M” is:
- the prefix milli- (1/1000) or
- the prefix mega- (1 million) or
- the size meter.
Each prefix (multiplicity) is always followed by a letter, such as “Mbit” or “MΩ.” With meters, things get more complicated:
- Nm (torque, actually: N•m) [size • size],
- mm (millimeter) [prefix • size],
- ms (millisecond) [prefix • size],
- m/s (speed) [ size ÷ size],
- W/mK (thermal conductivity, actually: W ÷ (m•K); “mK” is not the prefix of Kelvin!
Info: The multiplication sign is not defined as a single letter, but can consist of several different characters and is therefore typically missing.
Similarly, “K” stands for
- temperature in Kelvin (alone or combined, e.g., “K/W”) and
- as a prefix for “1000” like “KWh” or “kWh”.
https://ask.libreoffice.org/t/re-any-method-to-define-custom-units-in-lo-calc/122177
@koyotak what was that? A set of suddenly appearing new requirements? How does that relate to the original question? I provided a way to make the function(s) case-sensitive. Hopefully you can build on that.
No, I don’t know. In general, there is the clause in section 2.4,
In an OpenDocument file, calculation settings impact formula recalculation, which can be the same or different from a particular application’s defaults. These include whether or not text comparisons are case-sensitive, or if search criteria apply to the whole cell.
So LibreOffice should at least document in the help, how it calculates. It seems LibreOffice ignores the setting of HOST-CASE-SENSITIVE for the LOOKUP function.
Compatibility with Excel formats is important, for LOOKUP including binary format.
1 Like
=MATCH(1;FIND($C1;$A$1:$A$6);0) finds C1 in A1:A6 case-sensitively, including any sub-strings.
2 Likes
That works! However, I don’t understand your formula: Please explain the criteria for ‘=MATCH(1;…)’ and ‘=MATCH(…;0)’. Does the function ‘=FiND()’ inherit its criterion for distinguishing between uppercase and lowercase letters to ‘=MATCH’?
You certainly do not want to use LOOKUP with text data. It does not do what most users expect it to do.
By default, MATCH and VLOOKUP work analog to LOOKUP, so you do not want neither:
VLOOKUP(x; range; col_no)
nor
MATCH(x; column)
You want either
VLOOKUP(x; range; col_no; 0)
or
MATCH(x; column; 0)
because both functions can work in “database mode”, with 0 as last argument, instead of “spreadsheet mode”. The original LOOKUP function does not support “database mode”.
FIND(…) returns 1 if “AbC” is found at the start position of a string and the MATCH function returns the cell position where the 1 is matched.
However, my suggestion will also match “AbCdefgh” because FIND also returns 1.
1 Like
Elementar:
I was never aware of that! That’s why these functions usually returned the correct result, but too often the undesired one.
What other functions am I am misusing characters/letters for mathematical/arithmetic operations? These are calibrated and limited to digits and operands. And I’ve long since stopped misusing digits and numbers as text. Unfortunately, and what’s special about it is that CALC can’t directly calculate with numbers beyond decimal and duality without laborious text transformations like ‘DEC2HEX()’ and ‘DECiMAL()’.
I’ve found only a few bridges that connect the mathematical/arithmetric continent (people of numbers) via the physical island empire (people of sizes) with the literary continent (people of words), such as: - ‘VALUE()’, - 'DATEVALUE(), - ‘FiND()’. Does a list of functions exist that are uniquely assigned to the two continents “TEXT” and “NUMBER” as well as to the island “SIZES”? Regarding the problem mentioned at the beginning, it is desirable to be able to switch the hermaphroditic functions to whether a distinction should be made (TRUE) between uppercase and lowercase letters or not (FALSE), for example, because the first letter of the first word is always capitalized and have to be founded by lowercase characters.
Back to the task mentioned at the beginning! Basic idea, algorithm, transformation: if TEXT → NUMBER → calculate, compare, operate → NUMBER → (TEXT). The difficulty is the first transaction by recognizing on the occasions of the separators by ‘U$ 10,000.00’ or ‘10 000,00 €’.
A useful example algorithm:
continuous listing a)…z): ‘=UNiCODE(“a”)+1’ => ‘UNiCHAR()’ “b” …
Virtually everything in a spreadsheet is a number. Even dates, times and booleans are numbers. Any text entered into a cell is a label of some number or formula. As soon as you start listing items of any kind, you are entering the realm of databases, but without having access to database features. Databases on spreadsheets are made of plastic. Useful toys for visualization and prototyping, but you can’t really work with them in the long run.
Most of the functions returning text are listed under category “Text”. A programming language like Python or Perl is by a magnitude more powerful and more consistent than this.
The vast majority of all the other function categories returns numbers or references to other cell contents.