OR(EXACT(Cell; Range)) does not work properly

[Dave Peterson]

Make sure you use ctrl-shift-enter to enter the formula.

If you do it correctly, you'll see that excel wraps your formula with curly
brackets {}.

 

[TOM]

Hello,

I want to compare the contents of a cell with the contents of a range of
cells by using the formula OR(EXACT(B1; A1:A300))

However, the result becomes only TRUE if the match is in the first cell of
the range, i.e. A1, if the match is in any other row it becomes FALSE. I
don't know why. It should be also TRUE id there is a match, shouldn't it?

Does anybody know the reason why the function does not work properly?

Is there another simple way to perform the desired task?



Happy New Year to everbody out there



Tom

 

[Guest]

Why are you using EXACT? Usually you only need that when you don't want to
get a match between upper and lower case text entries, e.g. APPLE shouldn't
match apple

otherwise it would probably be better to use

=ISNUMBER(MATCH(B1;A1:A300;0))

or

=COUNTIF(A1:A300;B1)>0

which don't require CSE

 

[Dave Peterson]

I used =Exact() because that's what the OP used.

And both =match() and =countif() are not case sensitive.

I'm guessing that the OP wanted the distinction made.

For example, I've seen some people would use V to represent a vacation day (8
hours) and v represent a half day (4 hours). (I wouldn't recommend this kind of
thing to anyone with my typing skills, though!)

===
But I bet you really wanted to ask the OP. <vbg>

 

[TOM]

COUNTIF does the job perfectly, thanks!!

The reason why I used EXACT is simple: I searched for "compare cell
contents" in Help and this is what I got for an answer: "Use the EXACT and
OR functions to do this task".

Regards

Tom