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...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
4.3 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
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:

hlookup demo file (10.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-17 05:00 UTC, Oliver Brinzing
hlookup_match.ods (11.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-27 07:46 UTC, Oliver Brinzing

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

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:


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]
Comment 6 Oliver Brinzing 2020-02-18 17:47:01 UTC
reproducoble with

Version: (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.