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

JMKnapp

New Member
Joined
Mar 11, 2015
Messages
6
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:

ABCDEF
1TeamValueSingle NamesResult
2Bill, Sue,
Carla-S, Dave
ABill
3Mark, Mary,
Oliver, Randy
AAdam
4Adam, Brad,
William
BSue
5Alex, Beth, Sue,
Adam, Amanda,
Bryan, Joe, Catherine,
Betty, Bill, Marco
BCarla-S
6ADave
7BMark
8BMary
9AOliver
10BRandy
11BBrad
12AWilliam
13BAlex
14BBeth
15AAmanda
16ABryan
17AJoe
18ACatherine
19BBetty
20AMarco

<tbody>
</tbody>

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.
 
Last edited:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,670
Members
410,567
Latest member
SCraig123
Top