[Solved] Lower vs Upper Case in VLOOKUP
[Solved] Lower vs Upper Case in VLOOKUP
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.
Last edited by ptownpapa on Wed Jan 23, 2019 5:04 pm, edited 1 time in total.
openoffice 4.1.3 on Wndows 10
Re: Lower VS Upper Case in VLOOKUP
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.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Lower VS Upper Case in VLOOKUP
Thank you, but what and where are "RegEx"? The functions I can handle. I really appreciate your assistance, again, thank youLupp wrote: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.
openoffice 4.1.3 on Wndows 10
Re: Lower VS Upper Case in VLOOKUP
RegEx is a shortened version of Regular Expression
Search the Help - F1 for regular expressions for more information.
Search the Help - F1 for regular expressions for more information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Lower VS Upper Case in VLOOKUP
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 inunder array-evaluation. (Do not enter the curly brackets explicitly. They are generated by Calc if you enter the formula with Ctrl+Shift+Enter.)
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))}
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München