Bug 37776 - EDITING Calc VLOOKUP function doesn't produce same results as Excel
Summary: EDITING Calc VLOOKUP function doesn't produce same results as Excel
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: easyHack
Depends on:
Blocks:
 
Reported: 2011-05-30 22:54 UTC by Chris Peñalver
Modified: 2015-12-18 10:02 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
vlookup testcase.xls (8.00 KB, application/vnd.ms-excel)
2011-05-30 22:54 UTC, Chris Peñalver 		Details
possible fix (990 bytes, patch)
2011-06-11 20:11 UTC, Takeshi Abe 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Peñalver 2011-05-30 22:54:02 UTC 
Created attachment 47347 [details]
vlookup testcase.xls

Downstream bug may be found at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/381543

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.
Comment 1 Regina Henschel 2011-05-31 11:36:03 UTC 
Yes. ODF1.2 allows a single reference as second parameter. VLOOKUP has to be improved.

I think, this is a candidate for the easy hack list.
Comment 2 GerardF 2011-06-01 01:01:40 UTC 
(In reply to comment #1)
> Yes. ODF1.2 allows a single reference as second parameter. VLOOKUP has to be
> improved.

Just 2 comments :
1. It is also the same thing for others search functions like MATCH.

2. MATCH(A1;A2;0) with both A1 & A2 contains "dog" don't work, but
MATCH(A1;{"dog"};0) returns 1.
Same for VLOOKUP, Array constant with single reference works.
Comment 3 Kohei Yoshida 2011-06-01 05:57:33 UTC 
Let's make this an EasyHack then.  The code for VLOOKUP is in ScInterpreter::ScVLookup().
Comment 4 Takeshi Abe 2011-06-11 20:11:55 UTC 
Created attachment 47856 [details]
possible fix

Let me attach a possible fix for this.
It is for both VLOOKUP and HLOOKUP.
Comment 5 Kohei Yoshida 2011-06-14 07:07:46 UTC 
Yup, that's the right fix.  I've pushed this to master & cherry-picked to the -3-4 branch with my sign-off.

For future reference, it would be great if you could send your patches to the mailing list with [PATCH] in the subject line.  Patches attached to bugzilla sometimes go unnoticed because we don't monitor it for patch submissions.

Anyway, thanks a lot for the fix.
Comment 6 Robinson Tryon (qubit) 2015-12-18 10:02:16 UTC 
Migrating Whiteboard tags to Keywords: (EasyHack)
[NinjaEdit]