Finding Keywords from Text String - Formula

[JackChang]

All-

I have a list of descriptions on tab one and a list of keywords on tab two. Is there a formula that looks up my keywords on tab two, against the list on tab one? I'm trying to find where they match.

For example, if I lookup my keyword 'BRP120AF' on tab one, it will match rows 1423, 1424, 1425 (CIR BRP120AF PLUG ON 1P20A AFC).

Either highlighting these rows, or having an 'X' in a blank column works.

Any ideas? Sample file attached.

Thanks in advance!

[Aussiebear]

I have removed the "Solved" signage as no solution has been provided so far

[snb]

CF:

=NOT(ISERR(SEARCH("BRP120AF";$A1))) 

Applies to $A$1:$A$2000

[Paul_Hossler]

I did it so that double clicking on a tag on Sheet2 will color the corresponding Sheet1 values green

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rLookup As Range, rSearch As Range, rFirstOne As Range
    Dim i As Long, n As Long
    Dim sLookup As String
    
    Set rLookup = Target.Cells(1, 1)
    
    If rLookup.Column <> 1 Then Exit Sub
    
    sLookup = rLookup.Value
    If Len(sLookup) = 0 Then Exit Sub
    
    Set rSearch = Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Columns(1)
    
    With rSearch
        .Interior.ColorIndex = xlColorIndexNone
        
        n = 0
        Set rFirstOne = Nothing
        For i = 1 To .Rows.Count
            If InStr(.Cells(i, 1).Value, sLookup) > 0 Then
                .Cells(i, 1).Interior.Color = vbGreen
                n = n + 1
                If rFirstOne Is Nothing Then Set rFirstOne = .Cells(i, 1)
            End If
        Next i
    End With
    
    If Not rFirstOne Is Nothing Then
        rFirstOne.Parent.Select
        Application.Goto rFirstOne, True
    End If
    
    MsgBox n & " matches found for " & sLookup


End Sub