Issue 8088 - VLOOKUP and 0 value
Summary: VLOOKUP and 0 value
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: 643
Hardware: All All
: P4 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
: 72781 (view as issue list)
Depends on:
Blocks:
 
Reported: 2002-10-06 14:47 UTC by oblomov
Modified: 2015-03-10 14:27 UTC (History)
6 users (show)

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