[Solved] Lower vs Upper Case in VLOOKUP

[ptownpapa]

When using VLOOKUP the letters a,b,c and d and their upper case all are "found" as lower case. The remaining letters - lower and upper case - are "found" as upper case. I.E. in an array with a,A,b,B,,,e,E,f,F a search for "A" or "a" finds "a" and a search for "f" or "F" finds "F". Is There a way around this or is that just the way Calc is written? Sorry - forgot - OS is Windows 10.

[Lupp]

VLOOKUP can match for RegEx if the option is enabled, and RegEx are always case-insensitive in AOO.
There is also an option making comparisons case-insensitive if enabled.

If you want to get comparison case-sensitive independent of the settings, you can use the EXACT() fiunction.
If you generally want to use RegEx with functions eligible for this, but want case sensitive comparisons nonetheless in specific cases you can use MATCH() e.g. like in =MATCH(TRUE();A$2:A$101="aBcAnDsOoN";0) and then select what you want with the help of INDEX().
VLOOKUP often is too inflexible, anyway.

[ptownpapa]

VLOOKUP can match for RegEx if the option is enabled, and RegEx are always case-insensitive in AOO.
There is also an option making comparisons case-insensitive if enabled.

If you want to get comparison case-sensitive independent of the settings, you can use the EXACT() fiunction.
If you generally want to use RegEx with functions eligible for this, but want case sensitive comparisons nonetheless in specific cases you can use MATCH() e.g. like in =MATCH(TRUE();A$2:A$101="aBcAnDsOoN";0) and then select what you want with the help of INDEX().
VLOOKUP often is too inflexible, anyway.

Thank you, but what and where are "RegEx"? The functions I can handle. I really appreciate your assistance, again, thank you

[robleyd]

RegEx is a shortened version of Regular Expression

Search the Help - F1 for regular expressions for more information.

[Lupp]

I have to apologise for using an abbreviation without explaining it.

The settings relevant in the context of the question are found in the dialogue opening on >Tools>Options>LibreOffice Calc>Calculate.
The first option 'Case-sensitive' NOT enabled causes comparisons to not distinguish upper and lower case when comparators (= , <, and the like) are evaluated. Matching functions (MATCH, VLOOKUP, HLOOKUP, LOOKUP) seem to be case-insensitive independent of the settings, but I don't know a specification to that effect. The same with SEARCH while FIND always is case-sensitive.

Anyway, the details are messed-up in my opinion, and probably some are depending on the version. Therefore I always use the same settings, and can then be rather sure to know the working of my sheets with that respect.

My recommended settings are:
'Case-sensitive' enabled
'Precision as shown' disabled
'Search criteria = and <> must apply to whole cells' enabled
('Automatically find column and row labels' disabled)

'Enable regular expressions in formulae': I personally prefer to have this enabled because I often use RegEx. For a user not knowing much about RegEx I would recommend to disable this option because otherwise there are expectable strange effects concerning "special characters" which will be suspected as bugs by such a user.

To force case-sensitive matching independent of the settings use MATCH() in a construct with explicit comparison for equality by EXACT() like in

Code: Select all

{=INDEX(B$2:B$101;MATCH(TRUE();EXACT(C2;$A$2:$A$101);0))}

under array-evaluation. (Do not enter the curly brackets explicitly. They are generated by Calc if you enter the formula with Ctrl+Shift+Enter.)