Bug 80259 - VLOOKUP returns wrong value when the source data is blank
Summary: VLOOKUP returns wrong value when the source data is blank
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.7.2 release
Hardware: x86 (IA32) All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-20 03:16 UTC by Kevin Suo
Modified: 2016-09-30 05:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test file: vlookup-blank-value.ods (21.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-20 03:18 UTC, Kevin Suo
Details
test file: vlookup-blank-value.ods (20.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-20 03:40 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-06-20 03:16:36 UTC
Steps to reproduce:

1. Open the attached ods test file;

2. Observe that cell B5 and C4 in sheet2 have VLOOKUP formula, and are showing the value as blank.
>> This is expected, as their source values (B5 and C4 in sheet1) are blank.

3. Click the auto filter icon in B1 or C1 of sheet2.
>> The first value in the filer is a "blank box". This is wrong, the filter should never give this result.

4. Copy cell B5 or C4 of sheet2, and paste only value to another empty cell.
>> the cell you pasted to is showing "0". This is wrong, as you see blank value in B5 and C4.

Current Behaviour: (See the descriptions in the above steps)

Expected:
a. Filter result in step 3 should treat the blank value as blank.
b. Pasted result in step 4 should be blank, rather than "0".

OS: Windows XP SP3
Version: 4.3.0.1
Build ID: 67f5430184326974072b65403ef1d9d934fc4481

Note: In Microsoft Office 2010, VLOOKUP always return "0" if the source cell is blank. I believe this is their bug.
Comment 1 Kevin Suo 2014-06-20 03:18:24 UTC
Created attachment 101390 [details]
test file: vlookup-blank-value.ods

P.S.:

When Unzip the ods file and observe content.xml:

In the sheet1 part, it shows the following for the blank cell:
  <table:table-cell /> 

But in the sheet2 part, it shows:
- <table:table-cell table:style-name="ce2" table:formula="of:=VLOOKUP([.$A5];[$sheet1.$A$1:.$C$6];2;0)" office:value-type="float" office:value="0" calcext:value-type="float">
  <text:p /> 
  </table:table-cell>
Comment 2 Kevin Suo 2014-06-20 03:40:34 UTC
Created attachment 101394 [details]
test file: vlookup-blank-value.ods

Please ignore the previous file.
Comment 3 ign_christian 2014-06-20 07:08:50 UTC
Just my personal opinion:

> 3. Click the auto filter icon in B1 or C1 of sheet2.
> >> The first value in the filer is a "blank box". This is wrong, the filter should never give this result.
I think it's ok so we could filter blank cells.

> 4. Copy cell B5 or C4 of sheet2, and paste only value to another empty cell.
> >> the cell you pasted to is showing "0". This is wrong, as you see blank value in B5 and C4.
I agree, should be no value.

Tested under LO 4.0.6.2 - Win7 x86
Comment 4 Kevin Suo 2014-06-20 07:32:15 UTC
(In reply to comment #3)

Thanks for testing.

> > 3. Click the auto filter icon in B1 or C1 of sheet2.
> > >> The first value in the filer is a "blank box". This is wrong, the filter should never give this result.
> I think it's ok so we could filter blank cells.

I don't think so. If you go to sheet1 and set autofilter on the data, you will see that in "normal" cases blank value are not shown in the filter results, but you can choose the "Empty" option located within "Top 10, Empty, Not Empty" to filter out the empty results.
Comment 5 ign_christian 2014-06-20 08:10:32 UTC
> I don't think so. If you go to sheet1 and set autofilter on the data, you
> will see that in "normal" cases blank value are not shown in the filter
> results, but you can choose the "Empty" option located within "Top 10,
> Empty, Not Empty" to filter out the empty results.
You're right, if cell have no value so "Empty" option should be enough. 
Now I agree with you :)
Comment 6 Kevin Suo 2014-06-20 08:19:23 UTC
(In reply to comment #5)

@ign_christian:
So do you reproduce this bug behaviour following my steps? If you can reproduce, please help to mark the status to NEW.

Thanks!
Comment 7 ign_christian 2014-06-20 08:28:31 UTC
@suokunlog, I confirm that behavior only in 4.0.6.2. 

Your report is against 4.3.0.1, so I think someone with 4.3.0.1 that have the right to set NEW ?

I also think this report should be splitted. The auto filter icon looks like enhancement request.
Comment 8 Kevin Suo 2014-06-20 08:35:04 UTC
(In reply to comment #7)

> Your report is against 4.3.0.1, so I think someone with 4.3.0.1 that have
> the right to set NEW ?

4.3.0.1 is the version I tested it, if you can reproduce in 4.0.6.2, then of couse the status can be set NEW, and the version should be changed to 4.0.6.2 accordingly to reflect the most early version this bug appears.

> I also think this report should be splitted. The auto filter icon looks like
> enhancement request.

The filter results issue should be related to the VLOOKUP value issue, so I reported as one report. It's OK for me to file a saparate bug report if the devs think they are different issue.

--> Set to NEW;
--> Changed version to 4.0.6.2.
Comment 9 Kevin Suo 2014-06-24 13:20:34 UTC
Also reproducible in Ubuntu 14.04 x86, 3.6.7 release.
Set version to 3.6.7.2 and OS to ALL.
Comment 10 QA Administrators 2015-07-18 17:43:36 UTC Comment hidden (obsolete)
Comment 11 Buovjaga 2015-10-22 11:00:25 UTC
Still confirmed.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: fcc2415ade6ae93710bbbda9f7e163045e323105
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-21_16:55:13
Locale: fi-FI (fi_FI)
Comment 12 Kevin Suo 2016-09-30 05:54:38 UTC
The following issue is now working fine:
> The first value in the filer is a "blank box". This is wrong, 
> the filter should never give this result.

The following issue remains:
> the cell you pasted to is showing "0". This is wrong, 
> as you see blank value in B5 and C4.
Will report separate issue for this one.

Mark as WORKSFORME.