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.