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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Brian Barker

>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: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...