One to Many lookup quandry

[Jlewis30]

Greetings,

I have a long list of Account IDs and "interests" that I need to use to validate data in another table, by that Account ID. For any given Account ID I can have from one to 15 Interests associated.

If I use vlookup I am only going to get the first match, but I need to report all of the interests of Contacts associated with each ID (in a single cell, preferably). I am wondering if there is a simple(ish) way that I can concatenate any Interest that matches the Account ID of a Contact in my table.

Contact	Account ID	Interests
Jill	abc1234	Jazz, Rock, Swing
Jack	def5678	Swing, Bluegrass

<tbody>
</tbody>

Account ID	Interest
abc1234	Jazz
abc1234	Rock
abc1234	Swing
def5678	Swing
def5678	Bluegrass

<tbody>
</tbody>

I am about to get all crazy bad and use subtotals to transpose columns to rows (MANUALLY) and create the concatenated result in my lookup table, but it is like 150,000 rows. I want to either automate creating the combined lookup table, or figure out how to get a "one to many" lookup.

 

[Jlewis30]

Oh duh. Working in Excel 2010, Win 7

 

[yearnintolearn]

see edited response below

 

[yearnintolearn]

You get the data for "interests" in seperate columns by going to Data>Text to Columns>Delimted>Check Comma>finish

 

[yearnintolearn]

Contact	Account ID	Interests
Jill	abc1234	Jazz	Rock	Swing	=COUNTA(C2:E2)
Jack	def5678	Swing	Bluegrass		=COUNTA(C3:E3)
					List of Account ID's	List of Interests
					=VLOOKUP(G6,B$2:E$3,COUNTIF(G6:G$6,G6)+1,FALSE)	=B2
					=VLOOKUP(G7,B$2:E$3,COUNTIF(G$6:G7,G7)+1,FALSE)	=IF(((VLOOKUP(G6,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G6,G6))),G6,INDIRECT("B"&MATCH(G6,B$2:B$3,1)+ROW($B$2)))
					=VLOOKUP(G8,B$2:E$3,COUNTIF(G$6:G8,G8)+1,FALSE)	=IF(((VLOOKUP(G7,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G7,G7))),G7,INDIRECT("B"&MATCH(G7,B$2:B$3,1)+ROW($B$2)))
					=VLOOKUP(G9,B$2:E$3,COUNTIF(G$6:G9,G9)+1,FALSE)	=IF(((VLOOKUP(G8,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G8,G8))),G8,INDIRECT("B"&MATCH(G8,B$2:B$3,1)+ROW($B$2)))
					=VLOOKUP(G10,B$2:E$3,COUNTIF(G$6:G10,G10)+1,FALSE)	=IF(((VLOOKUP(G9,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G9,G9))),G9,INDIRECT("B"&MATCH(G9,B$2:B$3,1)+ROW($B$2)))

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>