Created attachment 112459 [details] test ODS file VLOOKUP returns #N/A in the attached spreadsheet. The expected return value is "1". Steps to reproduce: 1. Open the attached ODS file; 2. Observe that cell B2 has formula "=VLOOKUP(A2,E1:F1,2,0)". A2 contains string "26*12-12-8 TL R1". Expected: Because Cell E1 contains the string "26*12-12-8 TL R1" to be lookup, so cell B2 should return "1" (cell F1) Current Behaviour: Cell B2 returns "#N/A". Win 7 X86 Version: 4.4.0.2 Build ID: a3603970151a6ae2596acd62b70112f4d376b990 Locale: zh_CN VLOOKUP is one of the most common used functions in spreadsheet, so this is a critical issue.
This issue seems the same as described in bug 49711. That bug was closed as NOTABUG per the following comment: https://bugs.freedesktop.org/show_bug.cgi?id=49711#c2 Which says disable "Allow regular expressions in formulas" solves the proble. However, in my opinion this is not the case, this is really a bug. "Allow regular expressions in formulas" means regular expressions in *FORMULAS* are allowed. But in the formula "=VLOOKUP(A2,E1:F1,2,0)" there are no regular expressions, only reference to cells. (The regular expressions are in cell references, not in *FORMULAS". If the devs insists that "Allow regular expressions in formulas" = "Allow regular expressions in formulas, including regular expressions in values returned by cell references", then the option "Allow regular expressions in formulas" should be disabled by default in Calc. Enabling this option will cause confuse for users (Who knows what special chars are regular expressions? and how many people know that we can use regular expressions in formulas?)
Well, let me count the reported regular expression issues which were closed as NOTABUG: Bug 83791 - SUMIF() not work with a string with parenthesis as criteria Bug 81395 - Function RICERCA(x;y;z) doesn't work Bug 72739 - MATCH function returns wrong result Bug 67633 - EDITING: SEARCH function doesn't find text in cells containing hyperlinks (Closed as FIXED, which is wrong status. hyperlinks contains "." which is a regular expression, this is the real cause of the bug) Bug 57368 - FORMULA SUMIF with brackets Bug 65043 - MATCH for text containing brackets returns #N/A Bug 43919 - "[" breaks vlookup Bug 42097 - Formula: Search criteria fail with parenthesis and bracket characters Bug 36740 - Function ZÄHLENWENN does not recognize a text with a '+' character ... (and there may be even more which didn't show in my bugzilla search results) (and there are even more issues the ordinary users encounter, don't know why their formula returns wrong value, but don't know where to ask questions / reporting bugs)
P.S. I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice user, but still didn't know the "Allow regular expressions in formulas" option. What about the other ordinary users? And what's the advantages of enabling this option by default? If a user knows what regular expressions are, and want to allow regular expressions in there formulas, then they can enable this option mannully. We should not enable it by default for all users.
Do I understand correctly that you propose "Set 'Enable regular expression' off by default" ?
(In reply to raal from comment #4) Yes correct. I set this as an enhancement request.
(In reply to Kevin Suo from comment #5) > (In reply to raal from comment #4) > Yes correct. I set this as an enhancement request. Changing summary.
(In reply to Kevin Suo from comment #3) > P.S. > I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice > user, but still didn't know the "Allow regular expressions in formulas" > option. What about the other ordinary users? > And what's the advantages of enabling this option by default? > > If a user knows what regular expressions are, and want to allow regular > expressions in there formulas, then they can enable this option mannully. We > should not enable it by default for all users. Sounds like a question for the UX Council of Elders (and young'uns). Status -> NEW Component -> ux-advise
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.