FINDING PARTIAL ENTRIES IN A CELL/ARRAY

[FARAZ QURESHI]

I have the range A1:A125000 with names of clients. In range C1:C15 there are
some of names (Partial) that I have to find in the A1:A125000. What sort of
formula should I place in B1:B125000 to return an "X" if the corresponding
cell in a consists any of the word in C1:C15?

Something like:

B1=If(isnumber(find(???,A1)),"X","")

to be copied down?

The problem is that C1:C15 do not contain names in full but in parts. Like I
want a cell entry Johnson be highlighted if John exists in C1:C15.

Seeking your expert advice.

Thanx in advance.

 

[Max]

Provided the range $C$1:$C$15 is fully populated w/o any blanks
In B1, copied down:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$15,A1)))>0,"x","")

Otherwise, eg for larger search ranges where it may not be immediately
obvious that there's no blanks within, use instead in B1, copied down:
=IF(SUMPRODUCT((ISNUMBER(SEARCH($C$1:$C$15,A1)))*($C$1:$C$15<>""))>0,"x","")

You could fortify it further, if necessary, with a TRIM for the search
range, ie:
=IF(SUMPRODUCT((ISNUMBER(SEARCH(TRIM($C$1:$C$15),A1)))*(TRIM($C$1:$C$15)<>""))>0,"x","")

Replace SEARCH with FIND if you need it to be a stricter, case sensitive
search
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik