Excel: Return multiple matches in a single cell wh

Question

I am working on a file that has two columns. The first column has simple three word sentences. The second one has single word keywords.

I’d like to be able search the first column, find all sentences that have a specific keyword and list them as delimited values next to the keyword.

Assuming a pipe (“|”) as a delimiter, I’d get something like this:

First Column
Very blue sky.
Red sky tonight. 
Blue sky forever. 
My red car. 
Red red red.

Second column is as follows:

Second Column
Blue
Red

Desired Solution (has 2 columns, Blue and Red are in the first column)

Second Column         Results Column
Blue                  Very blue sky. | Blue sky forever. 
Red                   Red sky tonight. | My red car. | Red red red.

Thanks!

Answer 1

Here is one way of doing it.

1. Open Visual Basic Editor (VBE) by pressing ALT+F11 key.
2. Insert a new module using Insert >> Module

3. Paste below code in the code pane.

   Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
   Dim rng As Range
   
   If strDelimiter = "" Then strDelimiter = "|"
   If IsMissing(blCaseSensitive) Then
       blCaseSensitive = False
   Else
       blCaseSensitive = True
   End If
   
   For Each rng In rngSource
       If blCaseSensitive Then
           If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
       Else
           If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
       End If
   Next
   
   If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
   
   End Function
   

Then you can use this function in sheet like any other normal function e.g.

=ConcatPartLookUp(B2,A2:A6)

Please note I have provided two more optional arguments which may prove useful in the long run. If you want to make it case sensitive and pass a different delimiter say "#" then you need to use:

=ConcatPartLookUp(B2,A2:A6,"#",TRUE)