Description: This seems to only happen with certain special characters (including ( open bracket which is used all the time in company names Steps to Reproduce: 1.create lookup table 2.create list of items to look up 3.use vlookup Actual Results: 4.gets #N/A Expected Results: Should get successful result, regardless of what is in string Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-GB Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Version: 7.2.0.0.alpha1+ / LibreOffice Community Build ID: 162f5a20095c6937030d23ee03fb8f72c51eefa1 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-GB TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-06-06_16:55:45 Calc: threaded
Created attachment 173270 [details] lookup example
Created attachment 173271 [details] vlookup additional examples
The sample documents have option "Tools -> Option -> LibreOffice Calc -> Calculate -> Enable regular expression in formulas" set. Therefore "*" (asterisk) has ha special meaning in VLOOKUP's SearchCriterion. Hence you would need, according to documentation [1], use =VLOOKUP("\Q" & C5 & "\E";tLook;2;0), to disable this setting for the current call. Alternative: Deactivate option "Tools -> Option -> LibreOffice Calc -> Calculate -> Enable regular expression in formulas" [1] LibreOffice Help - VLOOKUP https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152 From my perspective this is not a bug.
Please feel free to reopen, if you don't agree.
Created attachment 173277 [details] updated sheet showing all vlookup examples successful
Thank you, Uwe. I am at a loss as to how to resolve this kind of issue. There are just so many tips and tricks based on settings and if I was not checking and reporting, i would never have have been told about this. It is only because I build lots of checks into sheets that I noticed there was a problem. my solution was to F&R the offending characters. Had no idea that Uwe's solution was possible (partly because I have been using spreadsheets since 1982(?) Visicalc. And so you never consider looking for other solutions. NOTE: I do think that the default should be FALSE, because then at least you would get warned (as I was that there is a problem). If "the last value that is smaller than the criterion will be returned" then one may be operating under the illusion that all is OK. https://www.forbes.com/sites/timworstall/2013/02/13/microsofts-excel-might-be-the-most-dangerous-software-on-the-planet/?sh=373ae0d8633d I consider Calc to be the most valuable tool that project manager can have.