Hi earthandbody! If my understanding is correct...
The content of B2 is separated by a comma.
for example: B2 = ROCK,JOHNSON
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim sAddr As String
Dim LastRow1 As Long
LastRow1 = Sheets("Search").Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LastRow2 As Long
LastRow2 = Sheets("2014-2019").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("Search").Range("A5:P" & LastRow1).ClearContents
Dim searchVal As Range, arr, i&, j&
With Sheets("2014-2019")
arr = Split(Target, ",")
For i = 2 To LastRow2
For j = 0 To UBound(arr)
Set searchVal = .Rows(i).Find(arr(j), LookIn:=xlValues, LookAt:=xlPart)
If Not searchVal Is Nothing Then
searchVal.EntireRow.Copy
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Exit For
End If
Next j
Next i
End With
Set searchVal = Nothing
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub