Bug 98370 - VLOOKUP returns different result compare to Microsoft Office
Summary: VLOOKUP returns different result compare to Microsoft Office
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.5.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-03-03 02:59 UTC by Manatap Sitorus
Modified: 2016-03-03 13:25 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Attached File (324.50 KB, application/vnd.ms-excel)
2016-03-03 10:20 UTC, Manatap Sitorus 		Details
Correct File (130.60 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-03 10:33 UTC, Manatap Sitorus 		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 Manatap Sitorus 2016-03-03 02:59:32 UTC 
I am working with a file that was created using Microsoft Office Excel. The document contains VLOOKUP formula.

When I open it with LibreOffice Calc, the result is different with the Office version. 

The file is attached. The difference can be found in row 7 column E. Calc version returns "70116004", where as Office version returns "8997007010707"


Thanks.
Comment 1 GerardF 2016-03-03 09:39:58 UTC 
There is no file attached.
Comment 2 Manatap Sitorus 2016-03-03 10:20:13 UTC 
Created attachment 123187 [details]
Attached File
Comment 3 GerardF 2016-03-03 10:29:10 UTC 
(In reply to Manatap Sitorus from comment #0)

> The file is attached. The difference can be found in row 7 column E. Calc
> version returns "70116004", where as Office version returns "8997007010707"

There is no formula in column E, nor VLOOKUP in row 7.
There is just a mix of strings and numeric in "Kode Barang" field. May be related.
Comment 4 Manatap Sitorus 2016-03-03 10:33:19 UTC 
Created attachment 123188 [details]
Correct File

Sorry. Wrong file. I've uploaded the correct one.

Thank you.
Comment 5 GerardF 2016-03-03 13:25:42 UTC 
(In reply to Manatap Sitorus from comment #0)

> The file is attached. The difference can be found in row 7 column E. Calc
> version returns "70116004", where as Office version returns "8997007010707"

Which is also incorrect, but expected.
In E7 formula, 4th argument (0) of VLOOKUP is missing (present in other rows).
With this argument omitted (or =1), VLOOKUP returns erratic (wrong) result unless the 1st column of the search_table is sorted in ascendant order (it is not the case in your file). With both Calc and Excel.