Bug 87208 - VLOOKUP function returns NaN when copying formula
Summary: VLOOKUP function returns NaN when copying formula
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.4.1 release
Hardware: x86 (IA32) Windows (All)
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-12-10 21:11 UTC by Mark Stewart
Modified: 2015-07-18 02:12 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Sample of VLOOKUP Nan issue (30.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-10 21:11 UTC, Mark Stewart 		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 Mark Stewart 2014-12-10 21:11:43 UTC 
Created attachment 110700 [details]
Sample of VLOOKUP Nan issue

I have a VLOOKUP table in $F$2:$G$11 in the attached sheet.  I have data in cells A20:C27; I enter a VLOOKUP formula in A21:

=VLOOKUP(C21,$F$2:$G$11,2,0)

It looks up the correct value.  If I copy the formula to cell A22, the displayed value in A21 and A22 change to "NaN"

Changing the fourth parameter from 0 to 1 does not help.
Comment 1 raal 2014-12-10 21:53:08 UTC 
I can not confirm with LO 4.3.4,win7. 
Please, could you test with newer version? Setting bug as needinfo, set as unconfirmed again if problem still occurs in newer version. Thank you.
Comment 2 m_a_riosv 2014-12-10 22:48:28 UTC 
I think was happening with OpenCL activated,
Menu/Tools/Options/LibreOffice calc/Formula activating OpenCL for external hardware
and
Menu/Tools/Options/LibreOffice calc/calculate - regular expression deactivated.

Some work have been done with OpenCL, so if you want use it please update the LibreOffice version
All version here: http://downloadarchive.documentfoundation.org/libreoffice/old/?C=N;O=D

I think we can resolve as WORKSFORME, please if you are not agree reopen it, but 4.2 is end of life and won't have new patches for it.
Comment 3 Mark Stewart 2014-12-10 22:58:58 UTC 
I have updated to 4.3.4.1 as raal suggested; I am now getting a "0" instead of the correct value when copying the formula.  I turned off 

   Formula activating OpenCL for external hardware

as suggested by m.a.riosv, and the formula works correctly; I have changed the version 4.3.4.1.

So this only occurs for me if OpenCL is enabled.
Comment 4 Robinson Tryon (qubit) 2015-03-05 15:36:26 UTC 
REOPENED status is used for bugs that have assigned dev, provided patches, etc. We should use UNCONFIRMED in this case.

Mark: Can you still confirm bug with latest LibreOffice? (4.4.1)

Status -> NEEDINFO
Comment 5 m_a_riosv 2015-07-18 02:12:10 UTC 
Seems to works fine now with:
Version: 4.4.5.1 Build ID: 1b6df295803ea040dab1b48b5424da8d78d94cf0