Searching for partial text match in range

[mmulcahy]

I am trying to search a partial text match given a range, in this case
three columns, and return a true false answer as to whether or not the
data can be found. Column A is a given users name and colums B, C, and
D are variations of names and logins from various programs. I am trying
to get column E to have a true/false value. This is kind of what the
sheet looks like:

A B C D
NameFul Valor_Users ASA _User Now Users
Abatta, Josef ABBOTT Abdail Abu Accordino
Abdul, Rasad S. ABDUL ADIM MIZA Adams
Apu, Andrew S. AELE ALEN GREGG Allred
Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson
Ackerman, Brian J. ABNDROTH Allen Eddings Anderion
Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen
Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth
Adon, Dian ADAIS ANDRE J PAUIN Barbour


How can I do a partial search, for say just the last name of column A,
so all information up to the comma, and then compare it across the
entire range of b,c and d, and return a true false response in coumn E?
Is this possible? Any help would be VERY much appreciated. I apologize
for the terrible formatting.

 

[Biff]

Try this:

=COUNTIF(B$2$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")>0

Biff

 

[Biff]

Try this:

=COUNTIF(B$2$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")>0

Biff

 

[mmulcahy]

Biff,
If there anyway to modify it so that it will only search for the last
name from Column A. So essentially everything up until the comma, but
nothing after? because this returns 0 for everything still. Thanks for
your help.

 

[mmulcahy]

Biff,
If there anyway to modify it so that it will only search for the last
name from Column A. So essentially everything up until the comma, but
nothing after? because this returns 0 for everything still. Thanks for
your help.

 

[Biff]

Just remove the wildcards "*"

=COUNTIF(B$2$9,LEFT(A2,FIND(",",A2)-1))>0

Biff