Issue 16786

Summary:	Need better control of regexp in vlookup
Product:	Calc	Reporter:	cdunham <openoffice>
Component:	formatting	Assignee:	AOO issues mailing list <issues>
Status:	ACCEPTED ---	QA Contact:
Severity:	Trivial
Priority:	P4	CC:	issues, openoffice, pier_andreit
Version:	OOo 1.1 Beta2	Keywords:	rfe_eval_ok
Target Milestone:	---
Hardware:	Other
OS:	Linux, all
Issue Type:	ENHANCEMENT	Latest Confirmation in:	---
Developer Difficulty:	---

Description cdunham 2003-07-13 18:09:00 UTC

Doing simple keyed lookups with a string using vlookup and with regular 
expressions turned on may not give the required results. 
 
Sometimes, you will want to lookup a regular expression, other times you want the 
text treated literally (a list of free-form text data, user input say). The latter is 
not possible without turning regexps off altogether, which doesn't allow you to mix 
the two methods. 
 
example: 
 
=vlookup(A1;B1:J10;2) 
 
where A1 may contain text that has regexp-like qualities, but should be treaded 
as plain text. 
 
Filed as a defect, as there are no work-around, that I can tell, and this blocks 
functionality.

Comment 1 oc 2003-09-18 15:44:31 UTC

This is not a bug but a request for enhancement

Comment 2 bettina.haberer 2004-01-29 18:23:35 UTC

Doing simple keyed lookups with a string using vlookup and with regular 
expressions turned on may not give the required results. 
 Sometimes, you will want to lookup a regular expression, other times you want
the text treated literally (a list of free-form text data, user input say). The
latter is not possible without turning regexps off altogether, which doesn't
allow you to mix the two methods. 
 
example: 
 
=vlookup(A1;B1:J10;2) 
 
where A1 may contain text that has regexp-like qualities, but should be treaded 
as plain text. 

This issue is considered for OO.o later.

Comment 3 drking 2008-05-05 12:21:30 UTC

I agree that being unable to enable/disable regex in a cell is a bad thing. 
Same thing applies to 'whole cell matching'.

Possibly worse is that the regex setting is rather hidden away, so the user may 
be unaware that his results are wrong.

One way around this would be to introduce a new function, say SETTINGS, similar 
to STYLE. Whereas STYLE sets the cell colours etc, SETTINGS could control 
regex, whole_cell_matching and maybe other things.

Unfortunately STYLE was designed to return 0, which means it has to be wrapped 
in another function (eg T() or IF()) when used with text. STYLE appears to be 
unique to Calc.

A typical construction with SETTINGS if it also returned 0 might be:

=IF(SETTINGS(1; 0); ""; HLOOKUP(....))

An alternative if inline arrays were changed to be able to accept formulae:
={HLOOKUP(....); SETTINGS(1; 0)} entered in scalar mode.

I'm not entirely sure this is all good - there might be implications with ODFF, 
which has global regex/wildcard flags I think. I'm merely sharing an idea - a 
possible solution.

Comment 4 prowlerxpla 2010-01-08 15:23:09 UTC

I agree, is a defect, a workaround is to use SUBSTITUTE function (in Italian
SOSTITUISCI) that substitute the characters used in regular expression (* or + 
with \* and \+) to be used with the functions like HLOOKUP or COUNT.IF
(CONTA.SE($B$3:$B$1000;"="&SOSTITUISCI(SOSTITUISCI(L3;"*";"\*");"+";"\+"))
).
A solution could be a new function like HLOOKUP.NOREGEXPR where the text is
always considered plain text or add an optional parameter in the function (1 or
0 to have or not to have regular expression considered) but as suggested in
Italian list this could give some compatibility problems with microsoft office

Comment 5 bettina.haberer 2010-05-21 15:15:46 UTC

To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".

Comment 6 niklas.nebel 2010-09-28 15:40:40 UTC

*** Issue 44295 has been marked as a duplicate of this issue. ***

Comment 7 niklas.nebel 2010-09-28 15:42:26 UTC

*** Issue 99472 has been marked as a duplicate of this issue. ***