Issue 63259 - VLOOKUP trouble between version 1.1.5 and 2.0.1
Summary: VLOOKUP trouble between version 1.1.5 and 2.0.1
Status: CLOSED DUPLICATE of issue 58286
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.1
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2006-03-16 14:41 UTC by seb2000
Modified: 2006-04-07 11:36 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 seb2000 2006-03-16 14:41:54 UTC 
I use a file sxc made with OO version 1.1.5 (lets call it file1)
This file is linked to another file (sxc)(lets call it file2) via the "FindV"
function ("recherchev" in french).

When I open file1 with openoffice calc v 1.1.5 every cells are correctly set
according to datas contained in file2.
(This works also if file2 is converted to ods and formula changed to point to
the new ods file)

But when I open file1 with openoffice calc v 2.0.1 some cells are correctly set
according to datas contained in file2 but most of them have "NA" value.I tried
to convert file1 and file2 to ods format but the trouble remains.

I did not change the formula(except the filename extension from sxc to ods),I've
only converted the files to new format (ods).

Last test: when I open ,with version 1.1.5, file1 converted to ods pointing to
file2 converted also to ods , everything is fine!!

I'm not sure to be clear, if you need more details contact me.
Comment 1 dridgway 2006-03-21 03:12:03 UTC 
I believe the English equivalent of RECHERCHEV is VLOOKUP, not FINDV -- adjusted
Summary.

What arguments are you passing, and is your data sorted? The behavior of LOOKUP
on unsorted data changed with 2.0, see issue 58286, perhaps VLOOKUP did as well.
If you wish to pass unsorted data, you'll need to pass FALSE in the fourth argument.
Comment 2 seb2000 2006-03-21 19:50:14 UTC 
Arguments used are :
- The value I wan't to find (ex : $E9)
- Matrix where to search (this is the link to the external file)
  (Ex:'file:///C:/Xchange/composants.ods'#$Composants.$A$2:$J$101)
- Index of the column inside the matrix
  (EX:7)
- Boolean to specify if the matrix is sorted or not.
  (Ex: false, because datas are not sorted)
Here is the complete formula :
"=RECHERCHEV($E10;'file:///C:/Xchange/composants.ods'#$Composants.$A$2:$J$101;7)"

As I said in the first description, I have "#NA" inside some cells and 
as you told me, I've just added "false" argument inside the formula and I
receive "#NOM?" ("#NAME?") inside ALL cells.
Any idea?
Comment 3 dridgway 2006-03-22 00:14:45 UTC 
I don't know much about localized OO: perhaps, en francais, OO doesn't like
FALSE. Try entering the number 0, or FAUX? If that doesn't resolve your problem,
please upload an example spreadsheet (or spreadsheets) which demonstrate the
problem.
Comment 4 seb2000 2006-03-22 19:32:56 UTC 
That's it sir!!
"FAUX" or "0" instead of "FALSE".
Formula works fine but I think it's a bit strange to be obliged to set an
optionnal argument,isn't it??
Thanks a lot.
Comment 5 dridgway 2006-03-23 06:33:31 UTC 
I think if you read the docs carefully, you may discover that you were supposed
to either set that argument or have presorted your data already in 1.5. I'm glad
to hear it's now working for you. I'll close this issue as a dupe of issue 58286.

*** This issue has been marked as a duplicate of 58286 ***
Comment 6 frank 2006-04-07 11:36:05 UTC 
closed