matching entries by partial match

  • Thread starter Thread starter Robert Flanagan
  • Start date Start date
R

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
 
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
 
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
 
Many thanks. I'll be using it shortly.

Bob

Bernie Deitrick said:
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
Click to expand...
 
Back
Top