Issue 8088

Summary: VLOOKUP and 0 value
Product: Calc Reporter: oblomov <gip.bilotta>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P4 CC: danie.wessels, issues, Janetzko, kpalagin, maison.godard, oooqa
Version: 643Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description oblomov 2002-10-06 14:47:52 UTC

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 

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 

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 
Comment 5 frank 2002-11-28 11:21:14 UTC
Hi Falko,

1 4 u

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

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


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
Comment 10 frank 2007-02-16 14:30:17 UTC
*** Issue 72781 has been marked as a duplicate of this issue. ***