matching entries by partial match

[Robert Flanagan]

I have a list in a single column of several thousand phrases. I have
another list of key words, about 10 long. I want to identify any of the
entries in the large list that contain any of the key words. Partial
matches are ok. For example, if the key word is "John", and an entry in the
big list is "John Doe" or "Bill Johnston", this would be a match. Is there
a way to do this with one formula that returns true or false, or "found" and
"not found"?

Thanks,

Bob

 

[Bernie Deitrick]

Bob,

Array enter a formula like (enter using Ctrl-Shift-Enter)

=IF(SUM(ISNUMBER(FIND($A$2:$A$11,C2))*1)>0,"Found","")

Where A2:A11 has your list of keywords, and C2 is the first of your phrases - then copy copy to
match.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Bob,

My first formula is case sensitive - if case is not important, then use this array formula

=IF(SUM(ISNUMBER(FIND(UPPER($A$2:$A$11),UPPER(C2)))*1)>0,"Has one","")

HTH,
Bernie
MS Excel MVP

 

[Robert Flanagan]

Many thanks. I'll be using it shortly.

Bob

Bob,

My first formula is case sensitive - if case is not important, then use
this array formula

=IF(SUM(ISNUMBER(FIND(UPPER($A$2:$A$11),UPPER(C2)))*1)>0,"Has one","")

HTH,
Bernie
MS Excel MVP