Excel 2007 : check for list of substrings with list of strings

It would help if you outlined the results - for ex. is the third item in your "Remaining List" to be considered a match or not ? [trees grow tall | tall trees ]

The answer to the above will (IMO) largely dictate whether or not you should be using VBA.

Originally Posted by rugbyfan

Initially I just wanted to find phrase matches, this would result in the example you chose not producing a match.

For the above a "standard":

C2:
=ISNUMBER(LOOKUP(9.99E+307,SEARCH(" "&$A$2:$A$3&" "," "&$B2&" ")))

where A2&A3 are your top keywords and B2 the first string in remaining list

Originally Posted by rugbyfan

it would be very beneficial if it could find the phrase matches even if the words were not in order

For the above you might want to consider a UDF.

Below not really tested in depth...

Function KeywordFound(rngK As Range, rngC As Range, _
                Optional boolCaseSensitive As Boolean = False, _
                Optional strDelim As String = " ") As Variant
                
    Dim vK, vKW, lngR As Long, lngC As Long, lngW As Long, vbComp As VbCompareMethod, bMatch As Byte
    vbComp = IIf(boolCaseSensitive, vbBinaryCompare, vbTextCompare)
    vK = rngK
    For lngC = LBound(vK, 2) To UBound(vK, 2) Step 1
        For lngR = LBound(vK, 1) To UBound(vK, 1) Step 1
            bMatch = 1
            vKW = Split(vK(lngR, lngC), strDelim)
            For lngW = LBound(vKW) To UBound(vKW) Step 1
                If InStr(1, strDelim & rngC & strDelim, strDelim & vKW(lngW) & strDelim, vbComp) = 0 Then
                    bMatch = 0
                    Exit For
                End If
            Next lngW
            If bMatch = 1 Then Exit For
        Next lngR
    Next lngC
    KeywordFound = bMatch
End Function

called along the lines of:

D2:
=KEYWORDFOUND($A$2:$A$3,$B2)

with optional parameters for defining case sensitivity (assumed insensitive) & string delimiter (assumed space) - each of which can be explicitly specified as desired.