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 wrote:

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

--

Dave Peterson

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

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" wrote:

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 wrote:

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

--

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

daddylonglegs wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

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





"daddylonglegs" wrote in message
...
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" wrote:

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 wrote:

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

--

Dave Peterson