Issue 2168 - LOOKUP doesn't work on document imported from Excel.
Summary: LOOKUP doesn't work on document imported from Excel.
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: 641
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2001-11-11 03:43 UTC by Unknown
Modified: 2013-08-07 15:15 UTC (History)
3 users (show)

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


Attachments
Excel Spreadsheet experiencing trouble in OO with LOOKUP command (1.00 MB, application/octet-stream)
2002-01-11 19:22 UTC, jdgreen7
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2001-11-11 03:43:06 UTC
Hi,

VLOOKUP function (probabaly HLOOKUP too) does not work when the range is on an 
external worksheet (i.e. a worksheet other than its own).

This contrasts with MS Excel; and is a real show stopper at a time when we are 
trying to dump MS Excel in favor of OO. Could you look into it, and/or get back 
to me in case you need more details at adembaba@arayan.com

Cheers,
Adem
Comment 1 peter.junge 2001-11-12 10:54:08 UTC
Let's have a look.
Comment 2 peter.junge 2001-11-19 15:21:29 UTC
Hi Adem,
I cannot reproduce the issue in the way I understand your report.
VLOOKUP works for me. But I see two issues anyway.
1. You cannot use to AutoPilot functions to create the formula. This
was possible with StarOffice 5.2.
2. The result in the target file doesn't update as the source file
changes.
Did you mean one of these?
Regards, Peter
Comment 3 peter.junge 2001-11-19 15:22:46 UTC
Hi Adem,
I cannot reproduce the issue in the way I understand your report.
VLOOKUP works for me. But I see two issues anyway.
1. You cannot use to AutoPilot functions to create the formula. This
was possible with StarOffice 5.2.
2. The result in the target file doesn't update as the source file
changes.
Did you mean one of these?
Regards, Peter
Comment 4 peter.junge 2001-11-26 12:05:44 UTC
Hi Peter,

Thank you for getting back to me. I was not exactly regerring 
to the issues you described.

Mine was different, so let me try to elaborate a little,
and let me also use MS Excel metaphors. This is what I did:


I had two .XLS files, file1.xls and file2.xls.

in a worksheet in file1.xls there are VLOOKUP links to
a worksheet in file2.xls. This works in Excel, and I have
used this as a simple database-like solution since before
the days Excel was able to link to databases (ms access?).
So, I have quite a bit of these .xls files.

When I try to import those files OO v638c, I have to correct
the file refrences to suit the Linux way, but even when I do
that, VLOOKUP does not work.

And, even when I try to emulate the same setup with native OO
files, same problem: VLOOKUP can not reference external files.

Hope that helps,

Thank you again.

Cheers,
Adem.

PS: I could send you a couple of small files if you give
prior permission.

Comment 5 peter.junge 2001-11-26 12:19:31 UTC
Hi Adem,
thanks for your reply. As you might have noticed I pasted your latest
email into issuezila. This is because everybody in the community
should be able to follow bug tracking.
If you like to provide the documents you don't need to ask for any
permission. Just edit the bug and use the link 'create a new
attachement'. Please note that these files are public accessible. You
shouldn't put confidential data there.

Regards, Peter
Comment 6 peter.junge 2001-12-03 14:12:04 UTC
Hi Adem,
please attach the files you offered to this Bug.
Regards, Peter
Comment 7 peter.junge 2001-12-04 12:22:22 UTC
reassiged to Oliver
Comment 8 oc 2001-12-05 13:22:23 UTC
Hi Adem,

I can't reproduce your problem in OO638 and OO641, therefor it seems 
that the problem must be a speciality with the bugdocs. Please 
attach these documents.
Comment 9 oc 2002-01-10 09:58:03 UTC
Owner set to submitter
Comment 10 oc 2002-01-10 09:59:00 UTC
Hi Adem,

I can't reproduce your problem in OO638 and OO641, therefor it seems 
that the problem must be a speciality with the bugdocs. Please 
attach these documents.

Thanks, Oliver
Comment 11 jdgreen7 2002-01-11 19:22:57 UTC
Created attachment 929 [details]
Excel Spreadsheet experiencing trouble in OO with LOOKUP command
Comment 12 jdgreen7 2002-01-11 19:31:09 UTC
I'm experience the same problem with the above spreadsheet.  It's one
that's been updated from Excel 4 to Excel 97/2000 over the last 4 or 5
years.  I did not create it, but it's now my job to fix it when things
need to be updated.  We will need to use it until I develop a better
process.  I removed most of the important information.

Anyway, in OO, it has a problem with the LOOKUP command.  Take a look
at the BUDGET.XLS tab on cell F165.  That one gives errors (among
others).  I'd like to start using OO company wide if we can get some
of our larger documents and spreadsheets to be fully compatible.  This
is a fairly large spreadsheet.

Also, are Excel 4 macros even supported in OO?  Thanks a lot!  Love
the program.  My email address is greenja6@cse.msu.edu.
Comment 13 Unknown 2002-01-16 19:18:24 UTC
We're also experiencing this bug which has put a halt to our SO52 ->
OO641 transition. This is more serious for us than the daily
corruption we experience over NFS. We have numerous spreadsheets that
link to one central lookup spreadsheet. About half of the VLOOKUP's
work, the other half do not. I triple checked to make sure everything
is formatted identically between all of the sheets involved.
Comment 14 Unknown 2002-01-18 03:20:25 UTC
It appears that the VLOOKUP fails only if the lookup field (the first
parameter to VLOOKUP, or the first column in the lookup range)
contains a decimal point. We worked around this by removing all
decimal points from our lookup fields. Formatting all of the relevant
fields to TEXT does not solve the problem.
Comment 15 peter.junge 2002-02-06 10:09:23 UTC
Hi,
the latest coments have been very interesting. I have a look again.
Regards, Peter
Comment 16 peter.junge 2002-02-14 13:49:34 UTC
Hi,
a lot of time has gone by and a bunch of comments had been added. I
now see minimum three different issue mentioned here.
1. The original problem Adem Baba mentioned which cannot be reproduced
by Oliver neither by myself.
2. The problem Jason Green mentioned. 'Lookup' doesn't work in the
attached document. The problem is located in cell AC1 because our
implemantation cannot handle the column header. Just delete it and
everything works fine. I changed the summary concerning to this issue
and reasign it to Eike. I think 'Lookup' has to find the header
itself. I hope this can be fixed.
3. The issue Casey Harkins mentioned at the bottom. I already sent him
a PM asking for a seperate report.

Best regards, Peter
Comment 17 ooo 2002-02-19 13:27:20 UTC
First, what follows is only about the issue with the document Jason
attached.

After taking a deep and thorough look at it, I must say that this is
NOT A BUG. The LOOKUP() search vector has to be sorted (see also the
online  help) for the function to return a proper result if there
isn't an exact match of the queried value. Numbers are sorted before
strings, the column header is a string, therefor the function does
stop there not having found a value being less or equal to the queried
value, thus returning a N/A.

Now why does it work in Excel? This is another case of "document a
feature but implement it different". The online help states the same
as we do: the search vector has to be sorted: ...,-2,-1,0,1,2,...,A-Z.
Otherwise the function may return a wrong value. But what they don't
tell you: Excel seems to ignore all string cells (not just a "column
header") if searching for a numerical value, at least that is what I
found out by try and error. If searching for a string instead you'd
propably get a wrong result by including a "column header" in the
search vector.

So I change this issue type from DEFECT to ENHANCEMENT, we might
implement the "ignore strings if searching a value" behavior in the
future.
Comment 18 ooo 2002-12-09 14:17:09 UTC
Target to OOo 2.0
Comment 19 frank 2004-05-05 11:40:43 UTC
Due to time problems this is re-targeted to OOo later
Comment 20 thorsten.ziehm 2004-07-13 12:12:57 UTC
This task is retarget to OOo2.0. The product team has decided, that this is a
must feature for OOo 2.0. 
Comment 21 ooo 2004-08-19 17:55:41 UTC
After more thorough investigation I have to say that the behavior is not simply
"skip strings if looking for numbers". It is a mixture of "skip something
somehow if it doesn't match, but not always" (exact conditions yet unidentified)
and the results a binary search delivers when executed on a range that  isn't
sorted. Which per definition results in almost arbitrary values and is the
reason why also the Excel documentation states that if the range is not sorted
the result may not give the correct value. We can try to emulate some portions
of the behavior, but I won't implement all side effects of undefined behavior.
In the first place this is: don't expect correct output if the input data
doesn't meet the requirements.

Btw: the attached document contains old Excel4 sheet macros that can't be turned
off. The document can't be loaded in Excel if security is set to High, and I
didn't lower security to Medium on the test drive I used, so I don't know what
the macros would do. Maybe sort the range of data?
Comment 22 ooo 2004-08-27 16:31:51 UTC
Just one more comment to illustrate the "data is not sorted" thingy: I loaded
(with macros this time) the document in Excel. Column AC is not sorted. The
formula in F165 does some LOOKUPs on column AC and sums corresponding values
from column AL. The result in F165 is 10582. The values the formula looks up and
the corresponding values  are:

AC -> AL
6310 -> 7442.99
6330 -> 0
6540 -> 8430.64
5221 -> 0
6511 -> n/a

The sum of the corresponding values clearly differs from the sum of the LOOKUPs,
no matter what may have been found for the missing 6511 value. Don't expect that
OOoCalc will ever yield identical results for such broken data, even if the
column headers will be excluded from the data range. In fact you shouldn't trust
those results at all, no matter if loaded in Excel or in Calc.
Comment 23 ooo 2004-08-27 17:52:02 UTC
I must admit that I made a mistake and overlooked the /3*2 at the end of the
formula, so the value 10582 is correct in this case. But this is more
coincidence, I can modify the data range without touching the values in question
and get a completely different result, for example set AC125 to 9999. Somehow
the document works only because only lower values than those in question are
inserted in between, in this case the zeros.
Comment 24 ooo 2004-08-27 23:05:21 UTC
Implementing something I would call an almost broken binary search with fallback
to iteration. It yields identical results in this case..
Comment 25 ooo 2004-08-31 14:32:08 UTC
On branch cws_src680_lookup:

sc/inc/dociter.hxx 1.4.118.2
sc/source/core/data/dociter.cxx 1.10.118.3
sc/source/core/data/table3.cxx 1.16.118.2
sc/source/core/tool/interpr1.cxx 1.29.118.2

The behavior for LOOKUP(), HLOOKUP(), VLOOKUP(), MATCH() in sorted range modes:

When searching for a numerical value, string contents are ignored and don't
terminate a search. When searching for a string value, the very first string is
ignored if it doesn't match, as it could be a column header. Searches still
assume that the range is sorted and return the last value less than or equal to
the queried value. The search stops if a value greater than the value queried is
encountered. In case the data is not sorted the result may be arbitrary and not
correct.
Comment 26 ooo 2004-08-31 14:33:06 UTC
Reopen to reassign.
Comment 27 ooo 2004-08-31 14:33:35 UTC
Reassign to QA.
Comment 28 ooo 2004-08-31 14:33:52 UTC
Restore status.
Comment 29 frank 2004-09-01 15:38:54 UTC
Found fixed on cws lookup using Windows, Linux and Solaris versions
Comment 30 frank 2004-11-23 10:10:35 UTC
Found fixed on Master src680m62 using Linux, Solaris and Windows Build