https://bz.apache.org/ooo/show_bug.cgi?id=126362
Issue ID: 126362 Issue Type: DEFECT Summary: Vlookup may return incorrect values from list if there are similar list items Product: Calc Version: 4.1.1 Hardware: PC OS: Windows 7 Status: UNCONFIRMED Severity: normal Priority: P5 Component: editing Assignee: issues@openoffice.apache.org Reporter: wright...@hotmail.com Created attachment 84789 --> https://bz.apache.org/ooo/attachment.cgi?id=84789&action=edit 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. -- You are receiving this mail because: You are the assignee for the issue.