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.

 

[Ron Rosenfeld]

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.

 

[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.

 

[Ron Rosenfeld]

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?

 

[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.

 

[Ron Rosenfeld]

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.

 

[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.

 

[Ron Rosenfeld]

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.