Comparing text and returning boolean values

[nicoleeee]

Hi, I need to compare text values in one cell to text values in another range of cells. Basically I need a formula that tells me if the text in cell E406 equals the text in any of the cells between M3 and M403. I tried =E406=M3:M403, but that didn't work. If anyone knows how to do this please let me know!

[Ron Coderre]

How about this:

=ISNUMBER(MATCH(E406,$M$3:$M$403,0))

Does that help?
Ron

[bj]

if you only need to know if there is a match try
=vlookup(E406,M3:M403,1,false)
you will get an error message if there is no match

"nicoleeee" wrote:

>
> Hi, I need to compare text values in one cell to text values in another
> range of cells. Basically I need a formula that tells me if the text
> in cell E406 equals the text in any of the cells between M3 and M403.
> I tried =E406=M3:M403, but that didn't work. If anyone knows how to do
> this please let me know!
>
>
> --
> nicoleeee
> ------------------------------------------------------------------------
> nicoleeee's Profile: http://www.excelforum.com/member.php...o&userid=23442
> View this thread: http://www.excelforum.com/showthread...hreadid=389445
>
>

[Biff]

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=OR(E406=M3:M403)

Biff

"nicoleeee" <[email protected]> wrote
in message news:[email protected]...
>
> Hi, I need to compare text values in one cell to text values in another
> range of cells. Basically I need a formula that tells me if the text
> in cell E406 equals the text in any of the cells between M3 and M403.
> I tried =E406=M3:M403, but that didn't work. If anyone knows how to do
> this please let me know!
>
>
> --
> nicoleeee
> ------------------------------------------------------------------------
> nicoleeee's Profile:
> http://www.excelforum.com/member.php...o&userid=23442
> View this thread: http://www.excelforum.com/showthread...hreadid=389445
>

[RagDyer]

Try this:

=COUNTIF(M3:M403,E406)>0

For True or False,
Or, eliminate the ">0" to return a count of matches.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"nicoleeee" <[email protected]> wrote
in message news:[email protected]...
>
> Hi, I need to compare text values in one cell to text values in another
> range of cells. Basically I need a formula that tells me if the text
> in cell E406 equals the text in any of the cells between M3 and M403.
> I tried =E406=M3:M403, but that didn't work. If anyone knows how to do
> this please let me know!
>
>
> --
> nicoleeee
> ------------------------------------------------------------------------
> nicoleeee's Profile:
http://www.excelforum.com/member.php...o&userid=23442
> View this thread: http://www.excelforum.com/showthread...hreadid=389445
>

[RagDyer]

Any chance that your clock might be off?<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ron Coderre" <[email protected]>
wrote in message
news:[email protected]...
>
> How about this:
>
> =ISNUMBER(MATCH(E406,$M$3:$M$403,0))
>
> Does that help?
> Ron
>
>
> --
> Ron Coderre
> ------------------------------------------------------------------------
> Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
> View this thread: http://www.excelforum.com/showthread...hreadid=389445
>

[Ron Coderre]

The short answer: No, RagDyer, my clock is not off (sheesh)....but, read on
if you've got nothing better to do.

The long answer to the inferred question:
At first I was a little puzzled by the comment about my clock, then I
remembered . . .
A while ago, I mentioned that ExcelTip.com had begun uploading their forum
activity to the MS Excel newsgroups every few minutes, BUT only pulled from
there once per hour. I got back helpful responses about using a newsreader
and firewalls, but here's the gist:

If a user posts directly to the ExcelTip forum, then I figure that's where
they'll look for a response. Consequently, if I want them to get my response
with the least amount of delay (up to an hour), I post my reply on that same
forum.

After reading your post and seeing that it came from the MS Excel newsgroup,
I went there to respond. According to that forum, I was 4th in the response
line, even though, I was first on the ExcelTip forum and never even knew
there were other posts queuing up from the MS newsgroup. So I was both early
AND late! <vbg>

--
Regards,
Ron


"RagDyer" wrote:

> Any chance that your clock might be off?<g>
> --
> Regards,
>
> RD

[RagDyer]

I remember reading something about that.

So that's why sometimes answers are displayed here, that give the impression
that you're being ignored, and a "later" post is acknowledged, as if it were
the *only* response in the thread.

And ... I *DO* apologize!
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> The short answer: No, RagDyer, my clock is not off (sheesh)....but, read
on
> if you've got nothing better to do.
>
> The long answer to the inferred question:
> At first I was a little puzzled by the comment about my clock, then I
> remembered . . .
> A while ago, I mentioned that ExcelTip.com had begun uploading their forum
> activity to the MS Excel newsgroups every few minutes, BUT only pulled
from
> there once per hour. I got back helpful responses about using a
newsreader
> and firewalls, but here's the gist:
>
> If a user posts directly to the ExcelTip forum, then I figure that's where
> they'll look for a response. Consequently, if I want them to get my
response
> with the least amount of delay (up to an hour), I post my reply on that
same
> forum.
>
> After reading your post and seeing that it came from the MS Excel
newsgroup,
> I went there to respond. According to that forum, I was 4th in the
response
> line, even though, I was first on the ExcelTip forum and never even knew
> there were other posts queuing up from the MS newsgroup. So I was both
early
> AND late! <vbg>
>
> --
> Regards,
> Ron
>
>
> "RagDyer" wrote:
>
> > Any chance that your clock might be off?<g>
> > --
> > Regards,
> >
> > RD
>

[Ron Coderre]

No need to apologize, RD.

If you ever check the ExcelTip forum, you'll see that the same problem
occurs:
OP posts an issue, an immediate response solves the problem, OP thanks
responder....then..an hour later a flurry of posts from the MS form arrives
(usually around 5 minutes after the hour).

--
Best Regards,
Ron


"RagDyer" wrote:

> I remember reading something about that.
>
> So that's why sometimes answers are displayed here, that give the impression
> that you're being ignored, and a "later" post is acknowledged, as if it were
> the *only* response in the thread.
>
> And ... I *DO* apologize!
> --
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit!
> -------------------------------------------------------------------

[chandima]

hi,
I need to convert the value of number into its text.
eg: 2455 must convert as two thousand four hundred and fifty five.
if u know the answer please reply me.my e-mail [email protected]
thank you
chandima