Reference Formula

[User]

I need a way to find cells that contain information that is included in
another cell.
Example:
A B
1 John Smith Smith, John
2 Jane Doe ABC Company
3 Billy Jones Jones, Billy

I'd like to use conditional formatting to highlight cells B1 and B3 because
it contains a word that is also in cells A1 and A3 respectively.

Thanks

 

[smartin]

I need a way to find cells that contain information that is included in
another cell.
Example:
A B
1 John Smith Smith, John
2 Jane Doe ABC Company
3 Billy Jones Jones, Billy

I'd like to use conditional formatting to highlight cells B1 and B3 because
it contains a word that is also in cells A1 and A3 respectively.

Thanks

Assuming there are exactly two words to check from column A and they
will be separated by exactly one space, select B1:B3 and use this
formula in conditional formatting:

=OR(ISNUMBER(SEARCH(LEFT($A1,SEARCH
(" ",$A1)-1),$B1)),ISNUMBER(SEARCH(MID($A1,SEARCH(" ",$A1)+1,99),$B1)))

(N.B. I had to enter a hard line break for this post to make it clear a
space is between the quotes.)

If you meant "contains /both/ words" change the OR to AND.

 

[Bill Kuunders]

I had been waiting for a while for others to give you a better solution.
One way is to insert a couple of columns and split the names for each column
into two by using
the <data><text to columns> procedure.
After that you can create conditional formatting using the is formula
option
and using the formula =OR(C1=B1,C1=A1) for C1 and similar for D1
=OR(D1=B1,D1=A1)
copy the formal down using the format painter.