Created attachment 177188 [details] File to demonstrate the issue. Using unsorted vlookup (ie. last parameter is FALSE()), VLOOKUP returns "#N/A" even though the search array has exact match. To make things more strange, (re)saving this file in .XLSX format and opening it, the VLOOKUP functions properly. Items on the file: B6:C12 - Array with values B3 - Exact value from B9 C4 - Formula: VLOOKUP(B3;B6:C12;2;false()) The value shown on C4 is #N/A Version: 7.2.4.1 Build ID: 20(Build:1) CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: fi-FI (en_IE.utf8); UI: en-US Calc: threaded Steps to produce: Open the attached file Copy (ctrl-c) any of the values in B6:B12 and paste (ctrl-v) it to B3 See value on C4. If "#N/A" - it's error Save as... .XLSX Close file Open the .XLSX file See value on C4 - Now contains proper value.
Hi Eike, I thought you might be interested in this issue
This is not a bug. As explained in VLOOKUP documentation [1], the function supports regular expressions if enabled in the spreadsheet options. Your spreadsheet does enable regular expressions. Your strings include parentheses; and parentheses have special meaning in regular expressions (which your B3 is considered to be by VLOOKUP). So searching for regular expression "PORTAALI_SME_FI_101 (N2003361)" means searching for text "PORTAALI_SME_FI_101 N2003361" (note no parentheses), and using the last part as a group (e.g., for purposes of backreferencing it). The text in that form indeed does not exist in the search range. Either disable regular expressions, or use workarounds mentioned in the help article, that allow to disable treating characters as special regex expressions. [1] https://help.libreoffice.org/7.3/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152
(In reply to Jani from comment #0) > Save as... .XLSX > Close file > Open the .XLSX file > See value on C4 - Now contains proper value. Just to address this piece: since Excel (and OOXML) does not support regular expressions at all, opening that format indeed makes Calc to not use regular expressions (it uses wildcards instead, which could make other strings, e.g. having asterisks, behave similarly). Also worth mentioning, that wildcards (not regular expressions) are the default since LibreOffice 5.3 [1]. [1] https://wiki.documentfoundation.org/ReleaseNotes/5.3#Option_settings
Zeesh!! I feel so stupid on this one! Yes, I have regular expressions enabled since... Quite long time. So just in case someone has the same issue: "workaround" to avoid the situation in the enclosed file: The formula to be used in C4 =VLOOKUP(CONCATENATE("\Q";B3;"\E");B6:C12;2;FALSE())