[Upstream] Calc VLOOKUP function doesn't produce same results as Excel

[Kieran Fleming]

Binary package hint: openoffice.org

1) lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.2-1ubuntu5
  Candidate: 1:3.3.2-1ubuntu5
  Version table:
 *** 1:3.3.2-1ubuntu5 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages
        100 /var/lib/dpkg/status
     1:3.3.2-1ubuntu4 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages

3) What is expected to happen in LibreOffice Calc via the Terminal:

cd ~/Desktop && wget https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/381543/+attachment/596214/+files/vlookup%20testcase.xls && localc -nologo vlookup\ testcase.xls

is the file obtains the same results as Excel or Gnumeric using VLOOKUP.

4) What happens instead is it does not.

WORKAROUND: Use Gnumeric.

apt-cache policy gnumeric
gnumeric:
  Installed: 1.10.13-1ubuntu1
  Candidate: 1.10.13-1ubuntu1
  Version table:
 *** 1.10.13-1ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages
        100 /var/lib/dpkg/status

Original Reporter Comments: When using the VLOOKUP function, the second parameter is interpreted as an array in OpenOffice, but Excel treats it as a range. This means that Excel spreadsheets that use a single cell reference will work in Excel, but not in OpenOffice. It should be possible to fix this by treating a single cell reference as a single element array.

ProblemType: Bug
Architecture: amd64
DistroRelease: Ubuntu 9.04
Package: openoffice.org-core 1:3.0.1-9ubuntu3
ProcEnviron:
 PATH=(custom, no user)
 LANG=en_AU.UTF-8
 SHELL=/bin/bash
SourcePackage: openoffice.org
Uname: Linux 2.6.28-11-generic x86_64

[Adonis Papaderos (ado-papas)]

I'm not an expert on this, thought after reading
http://office.microsoft.com/en-us/excel/HP100698351033.aspx
I get the impression that openoffice behaves more closely to the ms vlookup specification than excel.

[Kieran Fleming (kieran-fleming)]

Well, I don't really want to get into the question of who is following the spec the closest, because we all know that MS and specs don't mix :)
I can't see a big problem with accepting a single value in this function, and it would help compatibility with Excel, which is good for users. I should also point out that I found this bug trying to edit my timesheet at work, so this is a problem that happens in the real world. Also, Gnumeric doesn't have this problem, so making this change wouldn't affect compatibility with that.

[penalvch (penalvch)]

Kieran Fleming, I am closing the bug task for LibreOffice as it has been fixed in the latest development version of Ubuntu - Oneiric Ocelot.

This is a significant bug in Ubuntu. If you need a fix for the bug in previous versions of Ubuntu, please do steps 1 and 2 of the SRU Procedure [1] to bring the need to a developer's attention.

[1]: https://wiki.ubuntu.com/StableReleaseUpdates#Procedure

 lsb_release -rd
 Description: Ubuntu oneiric (development branch)
 Release: 11.10

 apt-cache policy libreoffice-writer
 libreoffice-writer:
 Installed: 1:3.4.2-2ubuntu2
 Candidate: 1:3.4.2-2ubuntu2
 Version table:
 *** 1:3.4.2-2ubuntu2 0
 500 http://us.archive.ubuntu.com/ubuntu/ oneiric/main i386 Packages
 100 /var/lib/dpkg/status

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.

[penalvch (penalvch)]

No reference URL.