[Solved] Lower vs Upper Case in VLOOKUP

Discuss the spreadsheet application

[Solved] Lower vs Upper Case in VLOOKUP

Postby ptownpapa » Tue Jan 22, 2019 1:03 am

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
ptownpapa
 
Posts: 15
Joined: Mon Oct 09, 2017 2:58 am

Re: Lower VS Upper Case in VLOOKUP

Postby Lupp » Tue Jan 22, 2019 1:31 am

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Lower VS Upper Case in VLOOKUP

Postby ptownpapa » Tue Jan 22, 2019 3:42 am

Lupp 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.


Thank you, but what and where are "RegEx"? The functions I can handle. I really appreciate your assistance, again, thank you
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 15
Joined: Mon Oct 09, 2017 2:58 am

Re: Lower VS Upper Case in VLOOKUP

Postby robleyd » Tue Jan 22, 2019 4:02 am

RegEx is a shortened version of Regular Expression

Search the Help - F1 for regular expressions for more information.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2570
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Lower VS Upper Case in VLOOKUP

Postby Lupp » Tue Jan 22, 2019 12:34 pm

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   Expand viewCollapse view
{=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.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests