Download it now!
Bug 54497 - VLOOKUP is not case sensitive
Summary: VLOOKUP is not case sensitive
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.5.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-09-04 16:25 UTC by David Lynch
Modified: 2015-03-29 03:32 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Sample document showing how to use VLOOKUP correctly (8.95 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-03-26 22:27 UTC, Luke
Details
One more reason this is not a valid request (8.26 KB, application/vnd.ms-excel.12)
2015-03-27 05:26 UTC, Luke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Lynch 2012-09-04 16:25:35 UTC
a1:a4={"A","a","=char(192)","=char(224)"}
b1:b4={1,2,3,4}
=VLOOKUP(A1,$A$1:$B$4,2,0) is 1
=VLOOKUP(A1,$A$1:$B$4,2,0) is 1
=VLOOKUP(A1,$A$1:$B$4,2,0) is 3
=VLOOKUP(A1,$A$1:$B$4,2,0) is 3

The VLOOKUP documentation in LibreOffice:

"If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order."

In OpenDocument-v1.2-part2, 6.14.12
"If RangeLookup is FALSE or 0, DataSource does not need to be sorted and an exact match is searched. Each value in the first column of DataSource is examined in order (starting at the top) until its value matches Lookup. If no value matches, the #N/A Error is returned."

Both documents specify an *exact* match. In  3.5.5.3, VLOOKUP is not behaving in this manner.
Comment 1 drevicko 2012-09-24 02:34:56 UTC
I think your code snippet was meant to be something like this:

a1:a4={"A","a","=char(192)","=char(224)"}
b1:b4={1,2,3,4}
=VLOOKUP(A1,$A$1:$B$4,2,0) is 1
=VLOOKUP(A2,$A$1:$B$4,2,0) is 1
=VLOOKUP(A3,$A$1:$B$4,2,0) is 3
=VLOOKUP(A4,$A$1:$B$4,2,0) is 4

Note that char(192) and char(224) ARE detected as different..
Comment 2 Sören 2012-12-27 19:49:45 UTC
I can reproduce the problem in
Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0)
on Ubuntu 12.04 (x86)
Comment 3 Owen Genat (retired) 2013-04-28 01:05:08 UTC
The problem as reported in the Description and modified / corrected in comment #1 can be confirmed in TDF v.3.5.7.2 (Build ID: 3215f89-f603614-ab984f2-7348103-1225a5b) and TDF v4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3) running on Ubuntu 10.04 x86_64 2.6.32-46-generic. The results indicated in comment #1 are what I am seeing.
Comment 4 QA Administrators 2015-03-04 02:21:02 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1.2 or later): https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

Thank you for your help!

-- The LibreOffice QA Team
This NEW Message was generated on: 2015-03-03
Comment 5 David Lynch 2015-03-04 08:35:28 UTC
4.4.1.2 on Windows 8.1

Bug still present as originally reported ie

a1:a4={"A","a","=char(192)","=char(224)"}
b1:b4={1,2,3,4}
=VLOOKUP(A1,$A$1:$B$4,2,0) is 1
=VLOOKUP(A1,$A$1:$B$4,2,0) is 1
=VLOOKUP(A1,$A$1:$B$4,2,0) is 3
=VLOOKUP(A1,$A$1:$B$4,2,0) is 3

and *not* as reported in comment 1 (ie char(192) and char(224) ARE *not* detected as different)
Comment 6 Luke 2015-03-26 22:27:12 UTC
Created attachment 114390 [details]
Sample document showing how to use VLOOKUP correctly

QA Team,
Before accepting a bug like this we need to be very careful to review the definition of the function. This ensures that we will not break interoperability with ourselves or MS Office.

VLOOKUP Function

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
				

where:

   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   col_index    The column number in the table_array for which the
                matching value should be returned.

   range_lookup It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP finds an exact match. If an
                exact match is not found, the #N/A error value is returned.
				

Therefore, to perform a case-sensitive VLOOKUP, you need to combine the VLOOKUP functions with a helper functions.

You can find multiple tutorial on the Internet, such as:

https://www.google.com/search?q=VLOOKUP+is+not+case+sensitive&ie=utf-8&oe=utf-8


In my attached example, VLOOKUP is performing a case-sensitive Lookup for "joe"
Comment 8 Luke 2015-03-27 05:26:56 UTC
Created attachment 114394 [details]
One more reason this is not a valid request

It would break interoperability with Excel.
Comment 9 David Lynch 2015-03-27 11:37:33 UTC
The primary specification is the ODF at
<http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#VLOOKUP>:

If RangeLookup is FALSE or 0, DataSource does not need to be sorted and an exact match is searched. Each value in the first column of DataSource is examined in order (starting at the top) until its value matches Lookup. If no value matches, the #N/A Error is returned.

... an *exact* match is searched, no ifs or buts.

Of lesser importance is any workaraound for this bug. Libreoffice should not be acquiescing in bugs in Excel.
Comment 10 Luke 2015-03-27 22:29:36 UTC
Again, see  attachment 114390 [details] for an example of how to use VLOOKUP for a case sensitive search. While backwards compatibly, might be irrelevant to you, it's an important feature for many of our users.  attachment 114390 [details] produces the same results in LibreOffice, WPS Sheets, Excel, Google Sheets, gnumeric, and OpenOffice. 

If you can provide an example where LibreOffice's function is different than all of the other spreadsheets on the market, it's a real bug that needs to be fixed. Otherwise, we're not going to break backwards compatibility and interoperability by changing a 20+ year old function because you don't like to use it the way it was originally specified.