Apache OpenOffice (AOO) Bugzilla – Issue 126362
Vlookup may return incorrect values from list if there are similar list items
Last modified: 2017-05-20 09:54:58 UTC
Created attachment 84789 [details] Demonstration of errors in VLOOKUP In the accompanying example sheet, a table (CTab) has 2 columns (placed in columns G & H) Col 1 is a set of composite codes in ascending Alphabetical order eg B11Wz,B11Wz100,B11Wz135,B12Wz, etc Col 2 is a set of ascending numbers to identify the row Actual column A is an exact copy of CTab Column1 with elements A4, A5,...An ... Actual column B Has corresponding elements Bn, with the formula Bn = VLOOKUP(An;CTab;2;0) (Treat CTab as a sorted table) Similarly column C has elements Cn = VLOOKUP(An;CTab;2;1) (Treat CTab as an unsorted table) In both B and C, the value Bn or Cn returned should be the value in Column 2 of CTab Conditional formatting is set to show when this occurs (blue = correct) For most cases, the value in B & C is correct. ==================================================== Column B (Treat CTab as a sorted table) 11 of 83 errors The group beginning C21Wz return the values for BC21Wz etc This group all has the format (C+ string$) and return the values for (BC + string$) Note that there is also a group (RC + string$) which has no problems =========================== Column C (Treat CTab as unsorted table) 18 of 83 errors Rule;First glance: The shortest of a group e.g. B11Wz, B11Wz100, B11Wz135 gives the result for the last of the group. So B11Wz gives the result for B11Wz135 Without a full check, I think that at least the first 3 characters must be the same. (R25WXX and R26CXX are both correct) Sorry to leave this analysis somewhat unfinished but THERE IS A PROBLEM!!! This is a small simplified subset of a 700kb spreadsheet.
Comment on attachment 84789 [details] Demonstration of errors in VLOOKUP Additional to the attachment|: A simpler version of the showing the errors In cell J4 enter "=VLOOKUP(G4;CTab;1;0)" or "=VLOOKUP(G4;CTab;1;1)" Copy down wards. As Col G is the 1st column of CTab array, this should copy that column to J4. It doesn't.
i would say this issue is invalid, cause your "content.xml" contains: <office:body> <office:spreadsheet> <table:calculation-settings table:case-sensitive="false" table:search-criteria-must-apply-to-whole-cell="false" table:use-regular-expressions="false"/> change "search-criteria-must-apply-to-whole-cell" to "true" and it will work you can do this via the "tools - options" menu