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.
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..
I can reproduce the problem in Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0) on Ubuntu 12.04 (x86)
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.
** 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
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)
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"
Correct URL: https://www.ablebits.com/office-addins-blog/2014/08/19/vlookup-case-sensitive-excel/
Created attachment 114394 [details] One more reason this is not a valid request It would break interoperability with Excel.
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.
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.