How to Search Comma-Separated Content in a Cell against a List

[JMKnapp]

Hello, everyone. Thanks for taking the time to read my post and to try to help.

I have one column of comma delimited names (ie, "Bill, Sue, Carla-S, Dave") that I would like to search against another pair of columns -- one that has a single name per cell, and its neighboring column with an associated project value.

My description may not make it very easy to visualize, so here's an example:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team[/TD]
[TD][/TD]
[TD]Value[/TD]
[TD]Single Names[/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill, Sue,
Carla-S, Dave[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mark, Mary,
Oliver, Randy[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Adam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Adam, Brad,
William[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alex, Beth, Sue,
Adam, Amanda,
Bryan, Joe, Catherine,
Betty, Bill, Marco[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Carla-S[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Oliver[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Randy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Brad[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]William[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Beth[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Amanda[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Bryan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Catherine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Betty[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Marco[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My goal here is to comb the comma delimited names in column A for the value from column C that corresponds to the associated single names in column D. For instance, if my results display in column F, then F1 would catalog values of A, B, B, A. The order of the values listed in F1 wouldn't matter (A, B, B, A or A, A, B, B etc.) as long as the amount of As and Bs is correct.

In my real-life work, column A is in a different worksheet than columns C and D (but are in the same workbook). I assume that any formulas discussed here will be easy enough to modify across sheets, but I've presented by example as-is for simplicity's sake.

I'd rather do this through a formula than VBA code, but if VBA offers the most logical route to solution then I'm more than willing to head down that path instead.

As an added twist, the file that I'm working with needs to stay in 97-2003 .xls format.

If I've neglected to provide any info that would be helpful, please let me know.

Any assistance is greatly appreciated.

**EDIT** It's probably worth mentioning that there's no set number of or limit to the names that could be in the cells of column A. A cell could include a single name (with no comma), three delimited names, 11 delimited names or what-have-you.