[SOLVED] Find how many words from cell are found in an array

Find how many words from cell are found in an array

I have two columns with data. The first one has some terms and the other one contains single words. what I have

I'm looking for a way to identify which words from each cell from the first column appear in the second, so the result should look something like this (I don't need the commas): what I need

My question is somehow similar to Excel find cells from range where search value is within the cell but not exactly, because I need to identify which words are appearing in the second column and there can be more than one word.

I also tried =INDEX($D$2:$D$7;MATCH(1=1;INDEX(ISNUMBER(SEARCH($D$2:$D$7;A2));0);)) but it also returns only one word.

Solution

If you are willing to use VBA, then you can define a user defined function:

Public Function SearchForWords(strTerm As String, rngWords As Range) As String
    Dim cstrDelimiter As String: cstrDelimiter = Chr(1) ' A rarely used character
    strTerm = cstrDelimiter & Replace(strTerm, " ", cstrDelimiter) & cstrDelimiter ' replace any other possible delimiter here
    SearchForWords = vbNullString
    Dim varWords As Variant: varWords = rngWords.Value
    Dim i As Long: For i = LBound(varWords, 1) To UBound(varWords, 1)
        Dim j As Long: For j = LBound(varWords, 2) To UBound(varWords, 2)
            If InStr(1, strTerm, cstrDelimiter & varWords(i, j) & cstrDelimiter) <> 0 Then
                SearchForWords = SearchForWords & varWords(i, j) & ", "
            End If
        Next j
    Next i
    Dim iLeft As Long: iLeft = Len(SearchForWords) - 2
    If 0 < iLeft Then
        SearchForWords = Left(SearchForWords, Len(SearchForWords) - 2)
    End If
End Function

And you can use it from the Excel table like this:

=SearchForWords(A2;$D$2:$D$7)