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
Created attachment 171509 [details] file with example
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
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).