Apache OpenOffice (AOO) Bugzilla – Issue 2168
LOOKUP doesn't work on document imported from Excel.
Last modified: 2013-08-07 15:15:02 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
Let's have a look.
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
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.
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
Hi Adem, please attach the files you offered to this Bug. Regards, Peter
reassiged to Oliver
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.
Owner set to submitter
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
Created attachment 929 [details] Excel Spreadsheet experiencing trouble in OO with LOOKUP command
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.
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.
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.
Hi, the latest coments have been very interesting. I have a look again. Regards, Peter
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
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.
Target to OOo 2.0
Due to time problems this is re-targeted to OOo later
This task is retarget to OOo2.0. The product team has decided, that this is a must feature for OOo 2.0.
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?
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.
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.
Implementing something I would call an almost broken binary search with fallback to iteration. It yields identical results in this case..
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.
Reopen to reassign.
Reassign to QA.
Restore status.
Found fixed on cws lookup using Windows, Linux and Solaris versions
Found fixed on Master src680m62 using Linux, Solaris and Windows Build