Issue 98298 - vlookup not case sensitive
Summary: vlookup not case sensitive
Status: CLOSED NOT_AN_OOO_ISSUE
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOO300m9
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: needmoreinfo, oooqa
Depends on:
Blocks:
 
Reported: 2009-01-21 01:10 UTC by marspokane
Modified: 2009-07-12 18:36 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Look at the erroneous results in lines 553 and 555 column C (65.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-01-21 01:13 UTC, marspokane
no flags Details
File show that vlookups do not return correct result when set to exact and mix of upper and lower case codes being looked up. (13.20 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-01-27 03:22 UTC, marspokane
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description marspokane 2009-01-21 01:10:16 UTC
When using vlookup it reports the first (not case sensitive) item. I.E. in this
list in column A a lookup of HA1 would report back the results from Ha1 (Item 3)

Ha0 Item 1
HA0 Item 2
Ha1 Item 3
HA1 Item 4
Comment 1 marspokane 2009-01-21 01:13:04 UTC
Created attachment 59544 [details]
Look at the erroneous results in lines 553 and 555 column C
Comment 2 mhatheoo 2009-01-21 16:34:55 UTC
hey marspokane

I can understand this, but you are wrong, and I am strictly opposing naming this
an "issue".
Commonly, features using an index needs to be case-insensetive by default 
The more in OO.o, as the cells labeld with format "text" can not be set to 
"small" ore "big" letters, meaning having an automated conversion available.
A missing feature since ...

That is, why I filed http://qa.openoffice.org/issues/show_bug.cgi?id=64209.
a similar problem. 
Case-insensetive needs to be the default option for sorting/ indexing/ lookup/
grouping. 
That case-sensetive (as choosable in Option-menue/CALC) might be a good-to-have  
is another story - but even that is not working properly so far.
 
Martin 
Comment 3 rvojta 2009-01-26 20:54:07 UTC
If you want to perform case sensitive match, you should look at EXACT function.
To quote documentation [1]: "Matching is always case-insensitive - the case
setting on the Tools - Options - OpenOffice.org Calc - Calculate dialog does not
apply."

Look at [2]. There's an example how to make case sensitive lookup. It's for
Excel, but should work in OO.o too? Don't know, didn't test.

As this is not defect, closing as INVALID.

[1]
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_VLOOKUP_function

[2] http://support.microsoft.com/kb/214264/EN-US/
Comment 4 marspokane 2009-01-27 03:22:49 UTC
Created attachment 59689 [details]
File show that vlookups do not return correct result when set to exact and mix of upper and lower case codes being looked up.
Comment 5 marspokane 2009-01-27 03:25:34 UTC
See attached spreadsheet. If codes are listed and sorted the a vlookup is done
it returns the wrong results.
Comment 6 rvojta 2009-01-27 07:20:17 UTC
What do you mean by "return correct result when set to exact"? Do you mean
"Preferences - OpenOffice.org Calc - Calculate - Case Sensitive"? If yes, again,
to quote documentation "Matching is always case-insensitive - the case
setting on the Tools - Options - OpenOffice.org Calc - Calculate dialog does not
apply". If not, please, explain your "set to exact".
Comment 7 rvojta 2009-01-27 07:30:03 UTC
I read documentation again and you probably mean SortOrder/Mode (the last,
optional, argument to VLOOKUP) with exact.  If yes, I still don't see a bug
here. Your VLOOKUP has Mode set to 1 and according to documentation, lookup
column must be sorted (which is) and 
     If mode is 0 or FALSE, the left column of datatable may be unordered, and
the first exact match is found (searching from the top). And:

If there is an exact match, that is the row found; if there is more than one
exact match, *** the row found is not necessarily nearest the top ***

So, what's wrong? I don't see the bug and still thinks that this issue is INVALID.
Comment 8 marspokane 2009-01-27 14:29:19 UTC
If you review the spreadsheet you must be able to see that the mode is 1 (True
for exact match) and the results should be case sensitive then why does the
search in the spreadsheet I added to this issue of this range:
   A    B
1  Hs0	St Croix Gas Stv Bod
2  HS0	Security Gas Stve Bd

returns this result:
   A    B
30 Hs0	Security Gas Stve Bd
31 HS0	Security Gas Stve Bd

with B30 and B31 using the formula =VLOOKUP(A30;A$1:B$23;2;1)
Obviously it is returning the result of A2 which is not an exact case sensitive
match to A30 which should have filled B30 with the same results as B1
Comment 9 rvojta 2009-01-27 15:14:40 UTC
From where did you get that exact match is case sensitive?

Again, quote documentation:

Matching >>> is always case-insensitive <<< - the case setting on the Tools -
Options - OpenOffice.org Calc - Calculate dialog >>> does not apply <<<.

And 1 doesn't mean exact match. 1 means that the column is alphabetically sorted
and the row found is not necessarily nearest the top.

And to your sample file - look at your VLOOKUP function. You forget to use $ and
the second VLOOKUP lookups in A2:B24 instead of A1:B23, etc. Same applies for
remaining lines too.

I still thinks that this is invalid issue. Do you agree finally?
Comment 10 marspokane 2009-01-27 15:34:24 UTC
Per my last entry I changed the range to adjust the fixed range to A$1:B$23 and
the PROBLEM still exists.

Referring to my last entry...
How can Hs0 return the HS0 line and have the VLOOKUP be working just fine? It is
the WRONG ANSWER. Doesn't that count as not working?
Comment 11 marspokane 2009-01-27 15:35:25 UTC
How can I make this work case sensitive?
Comment 12 marspokane 2009-01-27 15:37:52 UTC
By the way, if the VLOOKUP 9or any of the lookups for that matter) do not lookup
case sensitive like Quatro Pro functions with its VLOOKUP then it is BROKEN. It
doesn't match the way it should. If Microsoft drives off a cliff, does
OpenOffice go over with it?
Comment 13 rvojta 2009-01-27 15:49:22 UTC
> Referring to my last entry...
> How can Hs0 return the HS0 line and have the VLOOKUP be working just fine? It is
> the WRONG ANSWER. Doesn't that count as not working?

Again, read documentation:

 - match is *always* case-insensitive
 - when set to 1, returned line is not necessarily nearest the top

What is not clear? It's absolutely clear.

> How can I make this work case sensitive?

I gave you two links in of my previous comments. There's an example how to
provide case sensitive match with EXACT function. Read it and apply to OO.o. If
it will not work, use users forums or users mailing list.

Closing as INVALID. This is broken from your point of view, but according to
spec & documentation, this behavior is valid.
Comment 14 marspokane 2009-01-27 16:12:30 UTC
This is ONLY INVALID because the program doesn't work correctly and it is
documented as being unable to do what the purpose of the VLOOKUP function is
which is...
Returns a value from a table column, in the row found by lookup in the first
column. 

If the value MATCHES a constant or variable in the first argument, why would it
be case-insensitive? How can I report this as a deficiency? Where does someone
with a logical argument go to get an answer? I have given a great example of how
this function is intended to be used. Quatro Pro does it correctly, Lotus 1-2-3
has it right, why does OpenOffice have to follow the lost leader over the wrong
cliff?
Comment 15 marspokane 2009-01-27 16:13:40 UTC
How do I report the need for this to change is what I really need help with I guess?
Comment 16 rvojta 2009-01-27 16:38:19 UTC
I'm sorry, from the developers point of view, it's invalid issue, because it
isn't defect even if I understand what's your goal is. 

You want to change behavior which is here for ages. And it's not trivial and I
doubt that you'll be successful. But you can try it, nothing against it.

Look at http://sc.openoffice.org/ and subscribe to users@sc mailing list to get
help how to proceed with case sensitive match. Or subscribe to dev@sc where you
can discuss this with developers. Be patient, they have lot of work.

To get more help, search for OpenOffice.org users forms (www.oooforum.org, ...).
You can get help there too.

Defect is something that doesn't work according to specification. If you want to
use IssueZilla for this, you can enter new issue and mark it with ENHANCEMENT.
Attach example with detailed description and wait ...

If you want faster response I recommend IRC or mailing list, not IssueZilla.
Comment 17 Mechtilde 2009-07-12 18:36:37 UTC
invalid -> closed