OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] vlookup

• From: Eike Rathke <erack@sun.com>
• To: office-formula@lists.oasis-open.org
• Date: Mon, 15 Sep 2008 13:40:57 +0200

```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

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

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

• References:
• vlookup
• From: Andreas J Guelzow <aguelzow@math.concordia.ab.ca>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]