Bug 48358 - EDITING - HLOOKUP fails to return correct results.
Summary: EDITING - HLOOKUP fails to return correct results.
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Spreadsheet (show other bugs)
Version: 3.5.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium major
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-04-05 14:18 UTC by Sigmund Puchacz
Modified: 2013-11-14 22:12 UTC (History)
4 users (show)

See Also:
i915 platform:
i915 features:


Attachments
Spreadsheet for looking up movies to watch (16.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-04-05 14:18 UTC, Sigmund Puchacz
Details
use of VLOOKUP compared to HLOOKUP (16.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-16 23:01 UTC, leo.moons
Details

Description Sigmund Puchacz 2012-04-05 14:18:37 UTC
Created attachment 59546 [details]
Spreadsheet for looking up movies to watch

Spreadsheet is attached. HLOOKUP always returns N/A rather than valid selection.
Comment 1 bfoman 2012-08-31 11:00:33 UTC
Confirmed with:
LO 3.5.6.2 
Build ID: own W7 debug build
Windows 7 Professional SP1 64 bit

Function works in Excel 2010.
Comment 2 Joel Madero 2012-11-21 19:50:51 UTC
I'm unable to get good results with Excel 2010, I do see the #N/A in LibreOffice but not sure if that's correct or not. 

bfoman: can you take a look at this one last time and mark as NEW if you know it's a bug. Appreciate it


Thanks all!
Comment 3 leo.moons 2012-12-16 20:58:00 UTC
HLOOKUP seems to work OK for me.

I fail to understand why submitter wants to use HLOOKUP in this particular case, VLOOKUP seems more suitable for me.
I do not understand either how submitter wants to use the HLUUKUP function, I have added VLOOKUP function in cell F10 and this works as it should be.

HLOOKUP should be used in a horizontal matrix

Working with Windows 7 and version 4.0.0 beta 1
Comment 4 leo.moons 2012-12-16 23:01:19 UTC
Created attachment 71607 [details]
use of VLOOKUP compared to HLOOKUP
Comment 5 Michel Rudelle 2012-12-19 14:29:04 UTC
(In reply to comment #3)
> VLOOKUP seems more suitable for me.
> 
> HLOOKUP should be used in a horizontal matrix
> 

I confirm that there is a wrong use of HLOOKUP and this is not a bug:

Complying with ODF standard:
Open Document Format for Office Applications (OpenDocument) Version 1.2
Part 2: Recalculated Formula (OpenFormula)Format
OASIS Standard 29 September 2011

HLOOKUP function is defined § 6.14.5 page 130:
"Summary: Look for a matching value in the first row of the given table, and return the value of the indicated row."
VLOOKUP function is defined § 6.14.12 page 136:
"Summary: Look for a matching value in the first column of the given table, and return the value of the indicated column."

In your example, you want to look in the first column, so you must use VLOOKUP.
Notice also that arguments of your function are incorrect (the 3rd argument should be an integer). For obtaining what you want, you must use:
=VLOOKUP (B4;A7:B148;2)


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.