Is it possible to match a cell's contents from a list ?

[kittronald]

Is it possible to create a formula that looks up values in a list and
verifies whether they appear in a cell ?

For example:

A1="This is a short text entry"

B1 contains a formula that looks up multiple values elsewhere to see
if they appear in A1 and returns "True" if a match is found.

The list contains the words "This" and "entry".



- Ronald K.

 

[Pete_UK]

Suppose your two words are in M1 and M2. Put this array* formula in
B1:

=SUM(--ISNUMBER(SEARCH(M$1:M$2,A1)))>0

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - you do not type these yourself. If
you subsequently edit the formula you must use CSE again.

You can accommodate more words by increasing the range M1:M2 to suit
your list. Copy the formula down if you have other phrases in column A
to test out.

Hope this helps.

Pete

 

[kittronald]

Ron and Pete,

Thanks guys, that does the trick.

Is there a way to do that without using an array entered formula ?



- Ronald K.

 

[kittronald]

Ron,

That'll do it.

My computer is a bit slow with array entered formulas and given the
number of cells this would run in, the calculation time would be quite
mind numbing.

Thanks again.



- Ronald K.

 

[kittronald]

Ron,

Just realized you replied to my last post.

I switched to your last formula and it's very fast.

Thanks again.


- Ronald K.