I am using the following code to search in the A column of a row for a name. If the name is found, it is placed in a column 2 over. I am trying to search against a list of names rather than one name. Is it possible to add an array into what I have to search a list found in another sheet?

 Sub SearchName()
    Application.ScreenUpdating = False
    With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
        .Formula = "=IF(MIN(FIND({""Text1"",9},A2&""C9""))<=LEN(A2),""Text1"")"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
To clarify, looking to make "text 1" a list of text in another sheet.
Cheers!