Conditional Format based on partial match of text

[Clay Canvas]

Hi,

This is probably an easy question for many of you... I have two sources of data that I'm compiling into one workbook. Both include names. The first list is names of people who have worked on a project (column A). The 2nd list is names of people within a specific department (column B). Neither list of names are in the same order. I'm trying to utilize a formula in conditional formatting that will change the text color if someone's name in column A can be matched up with a name in column B. If the name can be matched up I'd like one format. If it cannot be matched up, I'd like a different format. What formula / strategy would you suggest? Can I match up a portion of the name allowing for some differences between column A and column B? (example: Col A = apple Col B = 10apples )
Thanks for your help!

 

[Aladin Akyurek]

Condition 1

=MATCH($A2,B:B,0)

Condition 2

=ISNA(MATCH($A2,B:B,0))

 

[RalphA]

For some unknown reason, Aladin's Condition 2 colored all my column A red, the Conditional Formatting font color I chose!

I tried this, which seems to work. assuming you want to match a name in column A with one in column B, and that the data in in the range A2:B18
For a perfect match, Condition1 is:
=OR(A2=B$2:B$18)

Or, if you can accept a match with string limitation of 5 charcters (or whatever number you choose), then use:

=OR(LEFT(A2,5)=LEFT(B$2:B$18,5))

 

[Aladin Akyurek]

For some unknown reason, Aladin's Condition 2 colored all my column A red, the Conditional Formatting font color I chose!

...

How did you manage that?

BTW, if you want to exclude empty cells from consideration, you can expand the formulas...

Condition 1

=($A2<>"")*MATCH($A2,B:B,0)

Condition 2

=($A2<>"")*ISNA(MATCH($A2,B:B,0))

 

[RalphA]

I don't know what happened.

I have, in the range A35:A38, the letters a, b, c, d.
In B35:B38, I have empty cells.
In A35, I entered, as a conditional format:

Condition1, Formula Is, =MATCH(A35,B$35:B$38,0)
Condition2, Formula Is, =ISNA(MATCH(A35,B$35:B$38,0))

then copied down to A38. And, now, all cells in A35:A38 are red!

Are the formulas not correct?

 

[Aladin Akyurek]

I don't know what happened.

I have, in the range A35:A38, the letters a, b, c, d.
In B35:B38, I have empty cells.
In A35, I entered, as a conditional format:

Condition1, Formula Is, =MATCH(A35,B$35:B$38,0)
Condition2, Formula Is, =ISNA(MATCH(A35,B$35:B$38,0))

then copied down to A38. And, now, all cells in A35:A38 are red!

Are the formulas not correct?

If B35:B38 against which you match a value in A is empty, the second formula will be true, hence the result you get. Rightly so I'd think. If you'd want to check whether there is anything in B to match against...

=ISNA(MATCH(A35,B$35:B$38,0))*COUNTA(B$35:B$38)

 

[RalphA]

Thank you once again for your patient teaching, Aladin.

After your last post, I tried my example again, with proper data in column B, and, it worked as you predicted! Good!

I had gotten involved with the second part of the OP's request, that of matching part of a name, and thought that, in your previous posting, the Condition2 referred to matching part of a name. So, when I tried your two conditions on my original sheet example with partial names in B, why, of course, no match was found and I got all red results, just like my face!

I now changed the non-matching (your Condition2) color to bright green, and saw the non-matched names in A turn bright green. So, I am now pointed in the right direction.

Thanks once again, Aladin.

 

[Clay Canvas]

Thanks for both of your input... I really appreciate it! ...still learning...

 

[BJungheim]

If you want to be able to look for partial matches, try:

=MATCH("*"&$A1&"*",B:B,0)