Bug 127587 - hlookup and match fail for values outside defined range if range contains empty columns
Summary: hlookup and match fail for values outside defined range if range contains emp...
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calc-Function
  Show dependency tree / graph
 
Reported: 2019-09-17 05:00 UTC by Oliver Brinzing
Modified: 2020-05-04 12:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Attachments
hlookup demo file (10.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-17 05:00 UTC, Oliver Brinzing 		Details
hlookup_match.ods (11.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-27 07:46 UTC, Oliver Brinzing 		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 Oliver Brinzing 2019-09-17 05:00:53 UTC 
Created attachment 154211 [details]
hlookup demo file

steps to reproduce:

- open attached spredsheet document
- cell D16 shows #NV
- delete column E
- now cell D16 shows DD

worked with AOO 4.15 and Excel
Comment 1 Xavier Van Wijmeersch 2019-09-17 16:13:48 UTC 
confirm with

Version: 6.4.0.0.alpha0+
Build ID: 4cf9bb10945aaad487c756c8f282d03b9eea89ae
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: nl-BE (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 2 m_a_riosv 2019-09-17 23:24:47 UTC 
Repro with 6.3.

I doesn't happen with VLOOKUP()
Comment 3 Xisco Faulí 2019-09-18 11:50:41 UTC 
Regression introduced by:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=f0701470858f57a855ba57c0c2283e52953db327

author	Eike Rathke <erack@redhat.com>	2013-11-27 23:43:09 +0100
committer	Eike Rathke <erack@redhat.com>	2013-11-28 00:15:41 +0100
commit f0701470858f57a855ba57c0c2283e52953db327 (patch)
tree 0d200bc70e0496af73e1b74b4099f64e59d49bcf
parent a9e7f34c83101c7a3f478ae6d8c487f0ea1c9bfd (diff)
resolved fdo#71589 reimplemented horizontal range lookup

Bisected with: bibisect-43max

Adding Cc: to Eike Rathke
Comment 4 Oliver Brinzing 2019-09-27 07:45:53 UTC 
MATCH is also affected
Comment 5 Oliver Brinzing 2019-09-27 07:46:22 UTC 
Created attachment 154575 [details]
hlookup_match.ods
Comment 6 Oliver Brinzing 2020-02-18 17:47:01 UTC 
reproducoble with

Version: 7.0.0.0.alpha0+ (x64)
Build ID: aa58c380894dd384f6ce1efc62b3932136f2f477
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

Is there a chance to get this fixed in LO 7.0?
Comment 7 Eike Rathke 2020-05-04 12:06:53 UTC 
This is not a bug. Specifying a 4th argument of 1 (i.e. not 0) the lookup range MUST be sorted ascending, which with an empty cell between it is not. Range lookup results on not strictly sorted data are arbitrary.