You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2015/06/11 23:06:20 UTC
[Issue 126362] New: Vlookup may return incorrect values from list if
there are similar list items
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: wrightash@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.
[Issue 126362] Vlookup may return incorrect values from list if
there are similar list items
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126362
--- Comment #1 from wrigh <wr...@hotmail.com> ---
Comment on attachment 84789
--> https://bz.apache.org/ooo/attachment.cgi?id=84789
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.
--
You are receiving this mail because:
You are the assignee for the issue.
[Issue 126362] Vlookup may return incorrect values from list if
there are similar list items
Posted by bu...@apache.org.
https://bz.apache.org/ooo/show_bug.cgi?id=126362
brinzing <ol...@gmx.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |NOT_AN_ISSUE
CC| |oliver.brinzing@gmx.de
Status|UNCONFIRMED |RESOLVED
--- Comment #2 from brinzing <ol...@gmx.de> ---
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
--
You are receiving this mail because:
You are the assignee for the issue.