Check for text in multiple cells

[tommcbrny]

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom

 

[JLatham]

Not elegant, but it will work:

=IF(IF(A1="Bill",1,0)+IF(C1="Bill",1,0)+IF(E1="Bill",1,0)+IF(G1="Bill",1,0)+IF(I1="Bill",1,0)=1,"Just One Bill","")

of course you could substitute a cell address where you'd type in a name for
"Bill" in all of the formulas.

 

[Jacob Skaria]

You can try the below formula in cell K1

=IF(SUMPRODUCT((MOD(COLUMN(A1:I1),2)=1)*(A1:I1="Bill")),"Bill","")

If this post helps click Yes

 

[tommcbrny]

Works well, thank you. Is there a way to substitute a list of names for
"Bill" so that any name in the list "Names" will be printed if found in the
cells checked?

 

[tommcbrny]

Definitely works, thank you. Can I sub in a list of names for "Bill", so
that any name in the list "Names" will be printed if contained in the cells
checked?