Issue 19089 - VLOOKUP function not working properly
Summary: VLOOKUP function not working properly
Status: CLOSED DUPLICATE of issue 3851
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 RC3
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-09-05 01:11 UTC by philmperry
Modified: 2003-09-22 09:20 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description philmperry 2003-09-05 01:11:55 UTC 
I have not been able to get the VLOOKUP function to work with most data. If the
search criterion (first argument of the function) does not exactly match an
entry in the array a "#N/A" error is returned. If the fourth argument of the
function is TRUE, it is supposed to match the next lowest array value if not an
exact match. If FALSE, only exact matches are permitted. VLOOKUP is returning
the #N/A error for values which fall between array values, even though the
fourth argument was entered as TRUE.

Something which may have a bearing on this is that although I entered TRUE when
defining the function it was changed to "1" (and FALSE to "0"). If the function
code is looking for the string "TRUE" it's never going to get a match and will
always behave as if the fourth argument is FALSE.
Comment 1 frank 2003-09-05 09:10:55 UTC 
Please attach a document that shows your problem and also give a step
by step description how to reproduce it.

Frank
Comment 2 philmperry 2003-09-18 04:35:25 UTC 
Well, my floppy drive is broken and the LAN is broken, so I'll have to
show it to you here:

    A      B      C      D       E                            F
1 Sales   Comm.  Col.  Sales   Comm%                        Comm$
2 $0.00   10.0%   2    $140.00 =VLOOKUP(D2;A1:B4;C2;TRUE)   =D2*E2
3 $100.00 10.5%    
4 $200.00 11.0% 

Note that the 'TRUE' in E2 gets changed to '1'. For the Sales value
(D2) of $100.00, which exactly matches one of column A's values, E2
shows 10.5% (works correctly). For D2 of $140.00, E2 shows '#N/A'. A
WORKAROUND I just found while typing this in is to change A1:B4 to
A2:B4, but then the operation of VLOOKUP does not match Excel. Excel
permits the inclusion of column labels in the lookup table range,
while Calc apparently chokes on them. If you want to consider this an
enhancement request (to work Excel-style) instead of a bug, I could
live with that.
Comment 3 frank 2003-09-19 10:09:14 UTC 
Hi Phil,

Calc does not calculate with textvalues. Your construct mixes numbers
and text and therefore the lookup assumes the column is not a number,
as the first entry is a text.

So this maybe a double to Issue 3851.

I set the flags accordingly.

Frank

*** This issue has been marked as a duplicate of 3851 ***
Comment 4 frank 2003-09-19 10:09:32 UTC 
closed double
Comment 5 philmperry 2003-09-20 00:41:20 UTC 
Huh? All values given are numbers. Columns A, D, and F are formatted
currency, B and E are percent format, and C is a number. As I said,
there is a workaround (to NOT include the column titles), but then it
doesn't work like it does in Excel.
Comment 6 frank 2003-09-22 09:19:43 UTC 
Hi Phil,

the column header is text, the values are numbers. So you have a mixed
environment and the vlookup assumes that all of it is text. So it
can't work.

As it is not working like Excel is intentional, we are not a clone.

For more Info see the Issue mentioned in my latest comment.

Frank

*** This issue has been marked as a duplicate of 3851 ***
Comment 7 frank 2003-09-22 09:20:12 UTC 
Please leave it closed as it is a double !