Bug 141976

Summary: HLOOKUP not working when (..) in lookup field
Product: LibreOffice Reporter: Olaf <documentfoundation>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: CLOSED NOTABUG    
Severity: normal    
Priority: medium    
Version: 7.1.2.2 release   
Hardware: All   
OS: Windows (All)   
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: file with example

Description Olaf 2021-04-29 16:01:08 UTC 
Description:
a working hlookup does not work, when in the first row (where the lookup takes place) is a () contained in the source.
Example: 
WVERWEIS($J$9;$J$11:$O$25;ZEILE()-3;0) works, when the first line is
Barcodes, QR-Codes, RFID und NFC
or
Backup und Datensicherung
but it shows a #NV when the first line is
Network Attached Storage (NAS) in Unternehmen und Privathaushalt


Steps to Reproduce:
1. put WVERWEIS($J$9;$J$11:$O$25;ZEILE()-3;0) in C6
2. have data in J9 limited to an area and have a dropdown there to choose items
3. place these in the area for the lookup of J9 which is
$Tabelle1.$J$11:$Tabelle1.$N$11

So, J11 holds
Barcodes, QR-Codes, RFID und NFC

K11 holds nothing (empty cell)

L11 holds
Network Attached Storage (NAS) in Unternehmen und Privathaushalt

M11 holds nothing (empty cell)
N11 holds
Backup und Datensicherung

Note: There might be another bug in the limited data field (J9 here). Even when ignoring empty cells (options for valid data) there is one empty line shown in the dropdown.

Actual Results:
When J9 contains
Barcodes, QR-Codes, RFID und NFC
hlookup works

Network Attached Storage (NAS) in Unternehmen und Privathaushalt		
hlookup fails and #nv is shown in C6

Backup und Datensicherung
hlookup works


Expected Results:
correct lookup 


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Locale: de
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: yes
Comment 1 Olaf 2021-04-29 16:02:11 UTC 
Created attachment 171509 [details]
file with example
Comment 2 Mike Kaganski 2021-04-29 16:56:14 UTC 
This is not a bug.

As described in HLOOKUP documentation [1]:

> The search supports wildcards or regular expressions. With regular
> expressions enabled, you can enter "all.*", for example to find the first
> location of "all" followed by any characters. If you want to search for a
> text that is also a regular expression, you must either precede every
> character with a "\" character, or enclose the text into \Q...\E. You can
> switch the automatic evaluation of wildcards or regular expression on and
> off in Tools - Options - LibreOffice Calc - Calculate.

The parentheses (and many other elements) are parts of Regular Expression syntax [2], and since your document has regular expressions enabled, the parentheses are considered capturing elements of regular expression, not literal search characters.

Make sure to not use regular expressions in formulas, or to properly mask the strings that should not be treated as regular expressions, as described in the help.

[1] https://help.libreoffice.org/7.1/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3151001
[2] https://unicode-org.github.io/icu/userguide/strings/regexp.html
Comment 3 Eike Rathke 2021-04-29 17:06:41 UTC 
As I had this ready in the edit buffer anyway..

Either disable regular expressions under Tools -> Options -> Calc -> Calculate (set to Wildcards for Excel compatibility, or no wildcards or regular expressions at all), or if you want to keep them enabled to be active for other expressions enclose the search text in \Q...\E to force literals, in your case

=HLOOKUP("\Q"& $J$9 &"\E";...)

(you could omit the closing "\E" in this case where it is the very last switching quoted literals off).