Combining Xlookup with IF and Conditional formatting

[Walkon]

My sheet contains two lists:

• Short List: A list of 100+ person names. The list is sorted in alphabetical order by last name and is (currently) located in Column J
• Long List: A list of the same names, each name appearing on the list five times, each time associated with a different organization. There are 60+ organizations. Each organization is a possible assignment for that person. Each person has identified five organizations they are interested in being assigned to. The list is sorted alphabetically by organization name and is (currently) located in Columns A and B

Short List:
J
Name
Mehrnaz Azziz
Terry Bush
Kyle Calso
Amanda Cunningham
John Doe

Long List:
A B
Name Organization
Amanda Cunningham Academy of Arts
John Doe Academy of Arts
Terry Bush Academy of Arts
Kyle Calso Allan Mackie House
Mehrnaz Azziz Allan Mackie House
Amanda Cunningham Allan Mackie House
Terry Bush Allan Mackie House
Mehrnaz Azziz Braintrust Educare

My task is to assign a single organization to each name. After each name is assigned an organization, a separate formula places an “x” in column K.

Short List
J K
Name Assigned
Mehrnaz Azziz
Terry Bush x
Kyle Calso
Amanda Cunningham x
John Doe

As there are more Names than Organizations, there are several names listed for each Organization.

To reduce clutter and confusion, after each person’s assignment has been made, I would like to use Conditional Formatting to Strikethrough every instance of that name on the Long List. Since each name is only assigned once, after they are assigned, there is no reason for their names to remain on the list. It doesn't matter which organization they are assigned to, simply that they are assigned.

A B
Name Organization
Amanda Cunningham Academy of Arts
John Doe Academy of Arts
Terry Bush Academy of Arts
Kyle Calso Allan Mackie House
Mehrnaz Azziz Allan Mackie House
Amanda Cunningham Allan Mackie House
Terry Bush Allan Mackie House
Mehrnaz Azziz Braintrust Educare

My idea is to use the IF function to determine if there is an “x” in column K beside a name in column J and if there is, to use Xlookup to find each instance of that name in column A and use Conditional Formatting to place the Strikethrough format on each instance.

I have been unable to create a formula(s) to accomplish this and am not even sure if it is possible. I look forward to the knowledge and ideas of others.

 

[Walkon]

You would not have actually typed that value. It would relate to what you had selected and what the active cell was when you entered the CF rule and/or how you entered the CF for a cell or range of cells and then perhaps copied the format to a different or larger range.

In any case, it doesn't really matter so long as you have it sorted now.

That sounds likely. Thank-you.
I hope you have a wonderful holiday season
Best,

Rob