Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||VLOOKUP and 0 value|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P4||CC:||danie.wessels, issues, Janetzko, kpalagin, maison.godard, oooqa|
|Issue Type:||ENHANCEMENT||Latest Confirmation in:||---|
Description oblomov 2002-10-06 14:47:52 UTC
Hello, in Excel, VLOOKUPing a 0 value will match an empty cell put before a 1; in Calc it does not (gives a #N/A). This also happens when VLOOKUPing an empty cell.
Comment 1 prgmgr 2002-10-19 18:26:21 UTC
Giuseppe, thank you for using and supporting OOo. Could you please step by step instructions on how to reproduce this error. In Excel on my test box, I cannot look up blank cells.
Comment 2 oblomov 2002-10-19 22:59:20 UTC
I did a little testings, and it seems to happen only when using Lotus 1-2-3 compatibility: if Tools -> Options -> Transition -> Transition Formula Evaluation is *checked*, empty cells will be looked up in Excel. Otherwise, they will give a #N/A.
Comment 3 prgmgr 2002-10-21 02:39:40 UTC
To be honest, I don't know if looking up blank cells is a great feature. I'll let the developers decide.
Comment 4 oblomov 2002-10-21 07:34:09 UTC
It could probably be implemented either with an option (VLOOKUP considers blank cell to have value 0) or with an extra parameter: VLOOKUP currently has two obligatory parameter and one optional boolean parameter; we could add one more optional boolean parameter, defaulting to "FALSE", which when changed to true reads blank cells as 0s. Or we could have both this per-formula option *and* a global option setting the default.
Comment 5 frank 2002-11-28 11:21:14 UTC
Hi Falko, 1 4 u Frank
Comment 6 falko.tesch 2003-11-04 16:49:00 UTC
Set target (based on PCD relevance)
Comment 7 maison.godard 2003-11-05 16:26:38 UTC
Hi, This is a quite annoying bug(?) when doing a mass migration from XLS to calc as a lot of formulas are broken showing Err:502 XLS sheets (Excell XP) interprets blank cells and the result is 0 Calc gives an empty cell as result So when LOOKUP is used in function calculations, the entire consistency of the spreadsheet is broken Could you change the target to correct this asap A lot of big migrations are starting and this behavior of import filter is a major drawback Thanks Laurent
Comment 8 daniewessels 2004-12-09 15:00:40 UTC
I also would desperately would like to see this fixed (asap)! If it was not for the vlookup incompatibility (between Office 2k and 1.1.3) of values on blank string values (and the difference of sheetname end-markers ! vs .) my conversion would have been perfect.
Comment 9 falko.tesch 2005-10-20 21:00:29 UTC
FT: I'm leaving so I will re-assign this issue to requirement default user