Bug 54567 - FILEOPEN: vlookup function does not work
Summary: FILEOPEN: vlookup function does not work
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.1.2 release
Hardware: x86 (IA32) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2012-09-05 16:23 UTC by Eduardo Soares
Modified: 2014-05-19 13:56 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
created in ms excel 2010 with vlookup functions (313.55 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-09-05 16:23 UTC, Eduardo Soares 		Details
XLSX converted do XLSB (235.11 KB, application/vnd.ms-excel.sheet.binary.macroEnabled.12)
2012-10-05 12:10 UTC, Eduardo Soares 		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 Eduardo Soares 2012-09-05 16:23:14 UTC 
Created attachment 66685 [details]
created in ms excel 2010 with vlookup functions

Problem description: 

Steps to reproduce:
1. open teste.xlsx (create in ms excel 2010)

Current behavior:
1 - the function VLOOKUP($A$1&CHAR(127)&$A6;TabRegistro;7;0) from first sheet does not work.
2 - an image from a map is shown

Expected behavior:
1 - the function should return values
2 - this image is not show in excel 2010

Windows 7 Sp1 (Windows NT 6.1)
Comment 1 Eduardo Soares 2012-09-05 16:28:07 UTC 
3 - sheet Codigos, cell D2, function COUNTIF(TabRegistro[cód];A5) also does not work.
Comment 2 Julien Nabet 2012-09-19 21:28:52 UTC 
I'm not sure it's a problem with VLOOKUP.
A column from Registro sheet contain this formula:
=[@Cód]&CAR(127)&[@X] 

Perhaps LO doesn't know how to interpret this. Could you create another column containing the concat of "Cód" and next column then copy paste value?
Of course it's not a solution, it's just for the test.
Comment 3 Eduardo Soares 2012-09-20 12:01:21 UTC 
After some tests con LO 3.6.1.1, I found these workarounds:

Problem 1: from sheet "registro", cell A2
it does not work: =[@cód]&CAR(127)&[@x]
it works: =$D2&CAR(127)&$E2

Problem 2: no solution yet (image still there)

Problem 3: from sheet "codigos", cell D2
it does not work: =COUNTIF(TabRegistro[cód];A2)
it works: =COUNTIF(Registro.$D$1:$D$1157;A2)

then...
the references to "tablename[column]" does not work like excel 2010.
Comment 4 Julien Nabet 2012-09-20 12:05:41 UTC 
Kohei: one for you?
Comment 5 Eduardo Soares 2012-10-05 12:09:25 UTC 
Tested on Win LO 3.6.2.2, still the same error.
But if I open the attached file on MS Excel 2010 and save it as XLSB format, and then open it on LO 3.6.2.2, there's no problem with the references to "tablename[column]".

Then:

Opening XLSX format I get error:

Excel 2010: =VLOOKUP($A$1&CHAR(127)&$A4;TabRegistro;6;FALSE)
LO 3.6.2.2: =VLOOKUP($A$1&CHAR(127)&$A4;TabRegistro;6;0)

Opening XLSB format then it works:

Excel 2010: =VLOOKUP($A$1&CHAR(127)&$A4;TabRegistro;6;FALSE)
LO 3.6.2.2: =VLOOKUP($A$1&CHAR(127)&$A4;OFFSET(TabRegistro;1;0;ROWS(TabRegistro)-1;COLUMNS(TabRegistro));6;FALSE())
Comment 6 Eduardo Soares 2012-10-05 12:10:37 UTC 
Created attachment 68116 [details]
XLSX converted do XLSB
Comment 7 Eduardo Soares 2012-12-13 00:53:28 UTC 
Also confirmed in version 4.0.0.0.beta1
Comment 8 Eduardo Soares 2014-05-19 13:56:06 UTC 
The problem with function VLOOKUP is now fixed on LO 4.2.4.2 on Linux AMD64

But the strange image from a map still showing, but on Excel it does not appear.