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.

On Mon, 11 Apr 2011 16:09:58 -0700 (PDT), kittronald wrote:

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.

If WordList represents a contiguous (no blanks) list of the words to check, then, with your data in A1:

This formula must be **array-entered**:

=OR(ISNUMBER(SEARCH(WordList,A1)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

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

On Apr 12, 12:09*am, kittronald wrote:
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.

Ron and Pete,

Thanks guys, that does the trick.

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



- Ronald K.

On Tue, 12 Apr 2011 13:49:03 -0700 (PDT), kittronald wrote:

Ron and Pete,

Thanks guys, that does the trick.

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



- Ronald K.

You could use:

=SUMPRODUCT(--ISNUMBER(SEARCH(WordList,A1)))0

But why? What's wrong with array-entered formulas?

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.

On Wed, 13 Apr 2011 15:57:06 -0700 (PDT), kittronald wrote:

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.

If speed is a significant issue, you could also try:

=SUMPRODUCT(COUNTIF(A1,"=*"&WordList&"*"))0

With just two functions, vs 3 in the previous, it might execute faster.

Ron,

Just realized you replied to my last post.

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

Thanks again.


- Ronald K.

On Fri, 29 Apr 2011 15:54:58 -0700 (PDT), kittronald wrote:

Ron,

Just realized you replied to my last post.

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

Thanks again.


- Ronald K.

Glad to help. Thanks for the feedback.