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.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Conditional Formatting like this?

Walkon.xlsm
	A	B								J	K
1	Name	Organization								Name
2	Amanda Cunningham	Academy of Arts								Mehrnaz Azziz
3	John Doe	Academy of Arts								Terry Bush	x
4	Terry Bush	Academy of Arts								Kyle Calso
5	Kyle Calso	Allan Mackie House								Amanda Cunningham	x
6	Mehrnaz Azziz	Allan Mackie House								John Doe
7	Amanda Cunningham	Allan Mackie House
8	Terry Bush	Allan Mackie House
9	Mehrnaz Azziz	Braintrust Educare
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=XLOOKUP(A2,J$2:J$6,K$2:K$6)="x"	text	NO

 

[Walkon]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Conditional Formatting like this?

Walkon.xlsm
	A	B								J	K
1	Name	Organization								Name
2	Amanda Cunningham	Academy of Arts								Mehrnaz Azziz
3	John Doe	Academy of Arts								Terry Bush	x
4	Terry Bush	Academy of Arts								Kyle Calso
5	Kyle Calso	Allan Mackie House								Amanda Cunningham	x
6	Mehrnaz Azziz	Allan Mackie House								John Doe
7	Amanda Cunningham	Allan Mackie House
8	Terry Bush	Allan Mackie House
9	Mehrnaz Azziz	Braintrust Educare
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=XLOOKUP(A2,J$2:J$6,K$2:K$6)="x"	text	NO

Good morning Peter,

Thank-you very much for your welcome and quick reply. The formula you supplied seems to partialy work. I very much appreciate it. I am rushed for time currently, but will take a closer look and explain what is occurring.

Thnkas again,

Rob

 

[Walkon]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Conditional Formatting like this?

Walkon.xlsm
	A	B								J	K
1	Name	Organization								Name
2	Amanda Cunningham	Academy of Arts								Mehrnaz Azziz
3	John Doe	Academy of Arts								Terry Bush	x
4	Terry Bush	Academy of Arts								Kyle Calso
5	Kyle Calso	Allan Mackie House								Amanda Cunningham	x
6	Mehrnaz Azziz	Allan Mackie House								John Doe
7	Amanda Cunningham	Allan Mackie House
8	Terry Bush	Allan Mackie House
9	Mehrnaz Azziz	Braintrust Educare
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=XLOOKUP(A2,J$2:J$6,K$2:K$6)="x"	text	NO

 

[Walkon]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Conditional Formatting like this?

Walkon.xlsm
	A	B								J	K
1	Name	Organization								Name
2	Amanda Cunningham	Academy of Arts								Mehrnaz Azziz
3	John Doe	Academy of Arts								Terry Bush	x
4	Terry Bush	Academy of Arts								Kyle Calso
5	Kyle Calso	Allan Mackie House								Amanda Cunningham	x
6	Mehrnaz Azziz	Allan Mackie House								John Doe
7	Amanda Cunningham	Allan Mackie House
8	Terry Bush	Allan Mackie House
9	Mehrnaz Azziz	Braintrust Educare
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=XLOOKUP(A2,J$2:J$6,K$2:K$6)="x"	text	NO

Good morning again Peter,

Thank-you again for your assistance. The XL2BB seems like a wonderful tool. I am trying it below for the first time. In brief, I inserted the formula you suggested and it partially worked. As (hopefully) seen below, 1) it appears to have stopped working for all at row 110 - this corresponds to the end of the list of names. 2) It worked for some names above that row (see Amanda Moore, lines 29, 444, 55, 86) and for some instances of some names above that row (see Courtney Somers, lines 2 and 65; Kacey Wemyss, lines 21 and 27). So I am not sure what I am doing wrong. I look forward to any advice you may be able to provide.

Walkon.xlsm
	A	B								J	K
1	Name	Organization								Name
2	Amanda Cunningham	Academy of Arts								Mehrnaz Azziz
3	John Doe	Academy of Arts								Terry Bush	x
4	Terry Bush	Academy of Arts								Kyle Calso
5	Kyle Calso	Allan Mackie House								Amanda Cunningham	x
6	Mehrnaz Azziz	Allan Mackie House								John Doe
7	Amanda Cunningham	Allan Mackie House
8	Terry Bush	Allan Mackie House
9	Mehrnaz Azziz	Braintrust Educare
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=XLOOKUP(A2,J$2:J$6,K$2:K$6)="x"	text	NO

 

[Peter_SSs]

As (hopefully) seen below, 1) it appears to have stopped working for all at row 110 - this corresponds to the end of the list of names. 2) It worked for some names above that row (see Amanda Moore, lines 29, 444, 55, 86) and for some instances of some names above that row (see Courtney Somers, lines 2 and 65; Kacey Wemyss, lines 21 and 27).

Unfortunately, what was below was my mini-sheet, not yours. The rest of your description doesn't mean much without being able to see your mini-sheet.

XL2BB does not readily show Strikethrough that is applied through Conditional Formatting (I had to trick it to do that above) so if you are trying to show us what is happening, I suggest that for the moment you apply Bold font in the CF rather than Strikethrough.

 

[Walkon]

Unfortunately, what was below was my mini-sheet, not yours. The rest of your description doesn't mean much without being able to see your mini-sheet.

XL2BB does not readily show Strikethrough that is applied through Conditional Formatting (I had to trick it to do that above) so if you are trying to show us what is happening, I suggest that for the moment you apply Bold font in the CF rather than Strikethrough.

Hi Peter,
Thank-you for your patience. I'll try this again

Mrexcel sheet.xlsx
	A	B								J	K
1	FullName	Organization								Full Name	Placed x
2	Courtney Somers	Academy of Inquiry and Adventure Okanagan								Nelli Acejo	x
3	Kevin Anderson	Academy of Inquiry and Adventure Okanagan								Alexis Allshorn	x
4	Cassidy Ashman	Academy of Inquiry and Adventure Okanagan								Kevin Anderson	x
5	Danika Liefke	Academy of Inquiry and Adventure Okanagan								Ariane Arbour	x
6	Bilal Madani	Academy of Inquiry and Adventure Okanagan								Cassidy Ashman	x
7	Kyle Sobon	Academy of Inquiry and Adventure Okanagan								Brennan Atamanchuk	x
8	Grayson Ball	Academy of Inquiry and Adventure Okanagan								Grayson Ball	x
9	Hannah Calso	Academy of Inquiry and Adventure Okanagan								Luisa Berger	x
10	Cassidy Scott	Academy of Inquiry and Adventure Okanagan								Megan Bernard	x
11	Blair Visscher	Academy of Inquiry and Adventure Okanagan								Julie Bertles	x
12	Emma Zarnke	Academy of Inquiry and Adventure Okanagan								Luke Bertolucci	x
13	Hannah Calso	Allan Brooks Nature Centre								Nicolas Brengarth	x
14	Domenic Smith	Allan Brooks Nature Centre								Sarah Brown	x
15	Julie Bertles	Allan Brooks Nature Centre								Marcus Bucek	x
16	Jenae Drisner	Allan Brooks Nature Centre								Chloe Burak-Baxter	x
17	Danika Liefke	Allan Brooks Nature Centre								Hannah Calso	x
18	Rylee Robinson	Allan Brooks Nature Centre								Selena Clark	x
19	Jakob Hamilton	Allan Brooks Nature Centre								Mehrnaz Coady Kobari	x
20	Jennifer Kemper	Allan Brooks Nature Centre								Claudia Corneil	x
21	Kacey Wemyss	Allan Brooks Nature Centre								Steven Cresswell	x
22	Bilal Madani	Allan Brooks Nature Centre								Kendel Cunningham	x
23	Kendel Cunningham	Balsam School								Lena Danyluk	x
24	Helen Elyse Johnston	Balsam School								Jenae Drisner	x
25	Emma Middleton	Balsam School								Kelly Duckworth	x
26	Kristen Nault	Balsam School								Kelsi Edgelow	x
27	Kacey Wemyss	Balsam School								John Eek	x
28	Jakob Hamilton	Balsam School								Jyllian Enos	x
29	Amanda Moore	Balsam School								Loran Evans	x
30	Sidney Nesbitt	Balsam School								Nathan Forestell	x
31	Laura Schellenberg	Balsam School								Dierdre Forrest	x
32	Cassidy Ashman	Balsam School								Kendyl Foster	x
33	Megan Bernard	Balsam School								Mackenna Galloway	x
34	Luke Bertolucci	Balsam School								Kendra Hallding	x
35	Jessica Hemrich	Balsam School								Jakob Hamilton	x
36	Bilal Madani	Balsam School								Dan He	x
37	Maya Peterson	Balsam School								Jessica Hemrich	x
38	Whitney Sigston	Balsam School								Baylee Hopwo	x
39	Vincent Timmermans	Balsam School								Gemma Howard	x
40	Angelina Veltri	Balsam School								Elyse Johnston	x
41	Luisa Berger	Balsam School								Jennifer Kemper	x
42	Steven Cresswell	Balsam School								Tatyjania Khounviseth	x
43	Amanda King	Balsam School								Amanda King	x
44	Amanda Moore	Balsam School								Alyssa Koziol	x
45	Evan Telford	Balsam School								Jaymie Kwiatkowski	x
46	Chloe Worrall	Balsam School								Nicole Labrecque	x
47	Jenae Drisner	Balsam School								Joshua Landie	x
48	Loran Evans	Balsam School								Noelle Lavallee	x
49	Jaymie Kwiatkowski	Balsam School								Mia Lecky	x
50	Mikayla Podmorow	Balsam School								McKenna Lesiuk	x
51	Camille Rivard-Sirois	Balsam School								Jessica Lewis	x
52	Kristen Tennant	Balsam School								Danika Liefke	x
53	Noni Phillips	BGC Okanagan								Kaitlin MacLeod Rogers	x
54	Baylee Hopwo	BGC Okanagan								Bilal Madani	x
55	Amanda Moore	BGC Okanagan								Jesse Manna	x
56	Grayson Ball	Braintrust Canada								Katrina Martel	x
57	Baylee Hopwo	Braintrust Canada								Emily McCormick	x
58	Sidney Nesbitt	Braintrust Canada								Jessica McIntyre	x
59	Cassidy Scott	Braintrust Canada								Christoph Messerer-Trosin	x
60	Jessica Lewis	Braintrust Canada								Emma Middleton	x
61	Callie Miller	Braintrust Canada								Callie Miller	x
62	Taylor Pilon	Braintrust Canada								Amanda Moore	x
63	Coral Ruttan	Braintrust Canada								Kristen Nault	x
64	Christoph Messerer-Trosin	Braintrust Canada								Sidney Nesbitt	x
65	Courtney Somers	Braintrust Canada								Kim Neville-Rutherford	x
66	Carolane Normandeau	Building Blocks Educare								Carolane Normandeau	x
67	Sydney Romanchuk	Building Blocks Educare								Emily Nottingham	x
68	Anastazia Ziros	Building Blocks Educare								Ally Perry	x
69	Megan Bernard	Building Blocks Educare								Maya Peterson	x
70	Jessica Hemrich	Building Blocks Educare								Noni Phillips	x
71	Jessica McIntyre	Building Blocks Educare								Taylor Pilon	x
72	Claire Taylor	Building Blocks Educare								Mikayla Podmorow	x
73	Angelina Veltri	Building Blocks Educare								Julia Pokropp-Motschko	x
74	Mia Lecky	Building Blocks Educare								Ryley Price	x
75	Sidney Nesbitt	Building Blocks Educare								Isabela Richard	x
76	Danika Liefke	Caetani Cultural Centre								Nicole Richardson	x
77	Julie Bertles	Caetani Cultural Centre								Camille Rivard-Sirois	x
78	Isabela Richard	Central Programs and Services								Rylee Robinson	x
79	Anastazia Ziros	Central Programs and Services								Sydney Romanchuk	x
80	NELLI KIM Acejo	Central Programs and Services								Mhari Runciman	x
81	Kelsi Edgelow	Central Programs and Services								Josh Russell	x
82	Jesse Manna	Central Programs and Services								Coral Ruttan	x
83	Serena Slonowski	Central Programs and Services								Jessica Sanders	x
84	Jeffrey Tookey	Central Programs and Services								Laura Schellenberg	x
85	Claudia Corneil	Central Programs and Services								Cassidy Scott	x
86	Amanda Moore	Central Programs and Services								Whitney Sigston	x
87	Samantha Skinner	Central Programs and Services								Amanda Singer	x
88	Cassidy Scott	Central Programs and Services								Samantha Skinner	x
89	Danielle Tompkins	Central Programs and Services								Serena Slonowski	x
90	Luke Bertolucci	Central Programs and Services								Domenic Smith	x
91	Selena Clark	Centre culturel francophone de l'Okanagan								Kyle Sobon	x
92	Julia Pokropp-Motschko	Centre culturel francophone de l'Okanagan								Courtney Somers	x
93	Samantha Temme	Centre culturel francophone de l'Okanagan								Claire Taylor	x
94	Mhari Runciman	Centre culturel francophone de l'Okanagan								Leah Taylor	x
95	Nathan Forestell	Centre culturel francophone de l'Okanagan								Evan Telford	x
96	Rylee Robinson	Clubhouse - Treehouse Forest Preschool								Sam Temme	x
97	Jade Zitko	Clubhouse - Treehouse Forest Preschool								Kristen Tennant	x
98	Noelle Lavallee	Clubhouse - Treehouse Forest Preschool								Vincent Timmermans	x
99	Maya Peterson	Clubhouse - Treehouse Forest Preschool								Danielle Tompkins	x
100	Leah Taylor	Clubhouse - Treehouse Forest Preschool								Jeffrey Tookey	x
101	Emma Middleton	Clubhouse - Treehouse Forest Preschool								Michael Vasko	x
102	Kim Neville-Rutherford	Clubhouse - Treehouse Forest Preschool								Angelina Veltri	x
103	Chloe Worrall	Clubhouse - Treehouse Forest Preschool								Blair Visscher	x
104	Sidney Nesbitt	Clubhouse - Treehouse Forest Preschool								Kacey Wemyss	x
105	Kristen Tennant	Clubhouse - Treehouse Forest Preschool								Amy Williams	x
106	Chloe Burak-Baxter	Clubhouse - Treehouse Forest Preschool								Stephen Wilson	x
107	Gemma Howard	Clubhouse - Treehouse Forest Preschool								Chloe Worrall	x
108	Jessica McIntyre	Clubhouse - Treehouse Forest Preschool								Emma Zarnke	x
109	Carolane Normandeau	Clubhouse - Treehouse Forest Preschool								Anastazia Ziros	x
110	Jenae Drisner	Clubhouse Child Care Sutherland								Jade Zitko	x
111	Sydney Romanchuk	Clubhouse Child Care Sutherland
112	Leah Taylor	Clubhouse Child Care Sutherland
113	Chloe Worrall	Clubhouse Child Care Sutherland
114	Jessica McIntyre	Clubhouse Childcare Cottonwoods
115	Sydney Romanchuk	Clubhouse Childcare Cottonwoods
116	Coral Ruttan	Clubhouse Childcare Cottonwoods
117	Megan Bernard	Clubhouse Farm
118	Jenae Drisner	Clubhouse Farm
119	Noelle Lavallee	Clubhouse Farm
120	Mia Lecky	Clubhouse Farm
121	Kim Neville-Rutherford	Clubhouse Farm
122	Maya Peterson	Clubhouse Farm
123	Laura Schellenberg	Clubhouse Farm
124	Chloe Worrall	Clubhouse Farm
125	Luisa Berger	Clubhouse Farm
126	Helen Elyse Johnston	Clubhouse Farm
127	Jaymie Kwiatkowski	Clubhouse Farm
128	Emma Middleton	Clubhouse Farm
129	Evan Telford	Clubhouse Farm
130	Vincent Timmermans	Clubhouse Farm
131	Emma Zarnke	Clubhouse Farm
132	Amanda Moore	Clubhouse Farm
133	Jessica Sanders	Clubhouse Farm
134	Kristen Tennant	Clubhouse Farm
135	Cassidy Ashman	Clubhouse Farm
136	Kendra Hallding	Clubhouse Farm
137	Gemma Howard	Clubhouse Farm
138	Tatyjania Khounviseth	Clubhouse Farm
139	McKenna Lesiuk	Clubhouse Farm
140	Danika Liefke	Clubhouse Farm
141	Amanda Moore	Clubhouse Farm
142	Rylee Robinson	Clubhouse Farm
143	Samantha Skinner	Clubhouse Farm
144	Michael Vasko	Clubhouse Farm
145	Kelsi Edgelow	Columbia Outdoor School & Blue Lake Camp
146	Amanda King	Columbia Outdoor School & Blue Lake Camp
147	Kristen Nault	Columbia Outdoor School & Blue Lake Camp
148	Emily Nottingham	Columbia Outdoor School & Blue Lake Camp
149	Luisa Berger	Columbia Outdoor School & Blue Lake Camp
150	Chloe Burak-Baxter	Columbia Outdoor School & Blue Lake Camp
Org by rank (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A110	Expression	=XLOOKUP(A1048575,J$2:J$110,K$2:K$110)="x"	text	NO

 

[Peter_SSs]

You have applied the Conditional Formatting incorrectly. You can see that from this

Select the whole of column A and then Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells

Now select from A2 down to the bottom of your column A data or as far as that column A data might ever go, but the active cell should be A2
Now apply the CF with rule

=XLOOKUP(A2,J$2:J$110,K$2:K$110="x"

 

[Walkon]

You have applied the Conditional Formatting incorrectly. You can see that from this

View attachment 53566

Select the whole of column A and then Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells

Now select from A2 down to the bottom of your column A data or as far as that column A data might ever go, but the active cell should be A2
Now apply the CF with rule

=XLOOKUP(A2,J$2:J$110,K$2:K$110="x"

Good morning Peter,

Thank-you very much for your patience and persistence. I have no idea how I entered A1048575 instead of A2, but at any rate the formula works perfectly now. I did notice that I have some data inconsistencies (double spaces beween given name and surname, etc.) so was able to clear them up. This formula has greatly improved by spreadsheet and will save me lots of time in the future, that-you again for your assistance.

Best,

Rob

 

[Peter_SSs]

I have no idea how I entered A1048575

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.