Re: [office-formula] vlookup

[Eike Rathke <erack@sun.com>]

Hi Andreas,

On Thursday, 2008-09-11 18:27:32 -0600, Andreas J. Guelzow wrote:

> VLOOKUP
> Summary: Look for a matching value in the first column of the given
> table, and return the value of the indicated column.
> 
> What does "the value of the indicated column" mean? 
> 
> Excel and Gnumeric are returning 0 if the cell in the indicated column
> is empty.  
> OOo appears to return the empty string.

Actually in the latest implementation of the upcoming OOo3.1 release the
emptiness is inherited, so subsequent conversions to either numeric 0 or
or empty string are possible. Something that Excel only half-heartedly
implements, i.e. while in one formula emptiness is inherited and
comparison on numeric 0 and empty string both yield TRUE, once the
result is a final formula cell result it loses the inheritance and is
converted to numeric 0. This leads to the following inconsistent
behavior in Excel:

A1: 1   B1: <empty>
        B2: =VLOOKUP(1,A1:B1,2) => 0

=B1=0                  => TRUE
=B1=""                 => TRUE
=VLOOKUP(1,A1:B1,2)=0  => TRUE
=VLOOKUP(1,A1:B1,2)="" => TRUE
=B2=0                  => TRUE
=B2=""                 => FALSE

In Calc, B2 is displayed empty as is B1, and now the expression =B2=""
also yields TRUE. For some more details on inheritance of emptiness and
examples see
http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=263

See also on this mailing list:

Date: Thu, 06 Mar 2008 20:05:30 +0100
Subject: [office-formula] Different moods of emptiness
From: Eike Rathke <erack@sun.com>
Message-id: <20080306190530.GF13043@sr1-eham02-01.Germany.Sun.COM>
http://lists.oasis-open.org/archives/office-formula/200803/msg00002.html

There was never a conclusion though. Also discussed briefly earlier, see
thread of

Date: Fri, 15 Dec 2006 14:52:23 +0100
Subject: [office-formula] SUMIF, COUNTIF, COUNTBLANK, DCOUNT, DSUM,
        ... with empty cells and "" and ="" empty string criteria
From: Eike Rathke <erack@sun.com>
Message-id: <20061215135223.GC23600@sr1-eham02-03.Germany.Sun.COM>
http://lists.oasis-open.org/archives/office-formula/200612/msg00001.html
continues also in
http://lists.oasis-open.org/archives/office-formula/200701/msg00000.html
because OASIS splits the archives by months :-(

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.

PGP signature