COUNTIF and VLOOKUP conflict with Options... Calculate [ ] and/or ... Calculate [?]

[Brian Barker <b....@btinternet.com>]

>At 20:49 17/07/2017 -0400, Emery E. St. Martin wrote:
>VLOOKUP works properly when I use "Tools Options... Calculate [ ] 
>Enable regular expressions in formulas". If I change .. Calculate [ 
>] .. to .. Calculate [tick] .. then my VLOOKUP function won't work properly.

Your problem with VLOOKUP() is the presence of parentheses in your 
search item, which have a meaning in regular expressions. Are the 
parentheses necessary or could you use alternative punctuation? You 
can avoid the problem by escaping the parentheses in your search 
item, using backslashes - as
DELAWARE TAX-FREE USA FUND A CLASS \(DMTFX\) .
Note that this will match the value in your array, notwithstanding 
that the array value does not have backslashes. (But conversely, if 
you have the backslashes in the search item, the match will not be 
found if you disable regular expressions in formulae.)

>COUNTIF works properly when I use... Calculate [tick]. COUNTIF does 
>not work properly when I use ... Calculate [ ].

It is odd (and error-prone) to add repeated data such as your "As 
of:" in every cell. Would it be easier if you put this somewhere else 
and entered the dates and times as proper spreadsheet dates and times 
- not as text?

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org