Excel Lookup with Multiple Values based on wildcard match

[Mike250]

Hi there,

Long time lurker, first time register and poster. I've searched through the boards but couldn't quite find what I was after.
I've got two worksheets, in the first (Sheet1) is simply a list of terms (tags) that will be referenced later on.

	A
1	Happy
2	Beach
3	Invitation
4	Super
5	Bliss

<tbody>
</tbody>

In the second Sheet (Sheet2) a table of pages and their titles:

	A	B	C	D
1	page_id	author_id	page_title	tag_list
2	21400	102	A Happy Person
3	21401	102	Superwoman
4	21402	102	Beach Bliss?
5	21403	102	The Invitation
6	21404	102	Back to the Future

<tbody>
</tbody>

I need to search Sheet 1 column A and if the word exists anywhere in Sheet 2 Column C then output the match in a comma separated list in column D.
So the word "Happy" is found in C2, so it gets written to D2.
The words "Beach" and "Bliss" are found in Sheet 2 C 4, so therefore D4 should have written: Beach, Bliss
The word "Super" is found in C3 (wildcard match to Superwoman) so D3 should have written: Super

End Result:

	A	B	C	D
1	page_id	author_id	page_title	tag_list
2	21400	102	A Happy Person	Happy
3	21401	102	Superwoman	Super
4	21402	102	Beach Bliss?	Beach, Bliss
5	21403	102	The Invitation	Invitation
6	21404	102	Back to the Future

<tbody>
</tbody>

Is there a VLOOKUP or INDEX function mash-up that I can use to achieve this? Thank you kindly in advance!

 

[Aladin Akyurek]

First, add the following code as a module to your workbook usingAlt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and invoke in D2 on Sheet2...

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(Sheet1!$A$2:$A$6,C2)),","&Sheet1!$A$2:$A$6,"")),1,1,"")

which you need to confirm with control+shift+enter, not just enter, and copy down.

 

[Mike250]

That worked a treat Aladin, thank you.

There is a trailing comma (,) but I can handle that.

Great solution, you are most kind.

 

[Aladin Akyurek]

That worked a treat Aladin, thank you.

You are welcome.

There is a trailing comma (,)

Strange. That shouldn't happen.

but I can handle that.

Great solution, you are most kind.

Thanks for providing feedback.