you don't need SUMPRODUCT.
That is enough to get the result:

=IF(ISNUMBER(FIND($F$1,A2)),B2,"")

In the other formulas you have a placeholder (asterix) also in front of
the substring. If the substring begins with the first character you get
an error.
FIND works in all cases.


Yes, works very well. 10,000+ rows,,, in a blink.

Sub Pn_Col_D()
Dim LRow As Long

LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Range("G2", Range("G2").End(xlDown)).ClearContents

With Range("G2").Resize(LRow, 1)
.Formula = "=IF(ISNUMBER(FIND($F$1,A2)),B2,"""")"
'.Formula = "=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"" "")"
.Value = .Value

End With

End Sub


Thanks again.

Howard