| Summary: | EDITING - HLOOKUP fails to return correct results. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Sigmund Puchacz <spuchacz> |
| Component: | Spreadsheet | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED NOTABUG | QA Contact: | |
| Severity: | major | ||
| Priority: | medium | CC: | bfo.bugmail, jmadero.dev, leo.moons, mchl.rdll |
| Version: | 3.5.2 release | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | Windows (All) | ||
| Whiteboard: | |||
| i915 platform: | i915 features: | ||
| Attachments: |
Spreadsheet for looking up movies to watch
use of VLOOKUP compared to HLOOKUP |
||
Confirmed with: LO 3.5.6.2 Build ID: own W7 debug build Windows 7 Professional SP1 64 bit Function works in Excel 2010. 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! 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 Created attachment 71607 [details]
use of VLOOKUP compared to HLOOKUP
(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.
Created attachment 59546 [details] Spreadsheet for looking up movies to watch Spreadsheet is attached. HLOOKUP always returns N/A rather than valid selection.