Using Conditional Formating to Highlight Similar Names in 2 Different Columns

[Steckles]

I'm trying to Highlight name in, say, column B of Sheet1 that are in the format 'Last, First Middle' that are also on a list of names in column C of Sheet 2 that are in the format 'Last, First'. I've tried using Wild Cards with the Match function in conditional formatting, but that only highlights names in the first column that don't have a middle name. Any help would be appreciated, thanks!

[Aardigspook]

Welcome to the forum.

Please attach a sample workbook (not a picture).
Make sure there is enough data to demonstrate your need but not so much that the file is too big (if you have 10,000 rows of data, you don't need to leave them all in, for example). Include before and after sheets in the workbook if needed to show the process you're trying to complete or automate. Make sure you include the answers you want to get - put examples in, with notes if necessary to explain details.
Remember to remove any confidential information or, if a cell needs data in it to work, overwrite it with made-up data.
The paperclip icon doesn't work at the moment, so click on Go Advanced under the reply box and then scroll down to Manage Attachments to attach a file.

[Steckles]

I've attached a lil sample here

[Steckles]

I've tried setting the Conditional Formatting formula to

Please Login or Register  to view this content.

but that just highlights every cell in the B column, and
I've tried

Please Login or Register  to view this content.

but that only highlights individuals without middle names.

[Aardigspook]

There's something in your file which keeps making Excel crash on me, but I managed to have it open long enough to see what you want.

1. Select your range that you want to format (in your file, B1:B7).
2. On the Home tab of the ribbon, click 'Conditional Formatting', then 'New Rule', then 'Use a formula to determine which cells to format'.
3. In the formula box, enter this:

Please Login or Register  to view this content.

Amend the Sheet2!$C$2:$C$6 references to match your actual range of Last, First names.
4. Click 'Format' then choose the highlight you want.

It works like this:
The Search function looks for each item in the search list (the Last, First list on Sheet2) within the cell (B1 initially, then B2 going down, etc).
For example: SEARCH({Bush, George";"Esparza,Plilipe";"Adams, John";"Rex, Timothy";"Smith, John"},"Adams, John Quincy")
That then becomes an array result containing the number at which the search term is found, like this (for these, the number will always be 1 since it's always at the start of the string/name):
{#VALUE!;#VALUE!;1;#VALUE!;#VALUE!}
The Lookup function then looks for its lookup-value (1E+100 which here just represents 'a very large number') in that array. It doesn't find it, so returns the last number found. In this case that's '1' but if there is no result, then the array contains only errors ({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}), so the lookup itself returns an error (#N/A).
The Iserror function does what it says - if there is an error, it returns 'True' if not it returns 'False'. So you get 'True' if there is not a match and 'False' if there is.
The Not function reverses this, to give you 'True' when there is a match.
...and that's what the Conditional Formatting needs - a 'True' to format the cell.

Hope that helps.

[Steckles]

Helped a bunch, Thanks!

[Aardigspook]

You're welcome, glad I could help. Thanks for the rep and for marking the thread as Solved.