Search for an array of strings in a single cell and display adjacent cell value

Hello I am new here and need help with the following.
I have a column A with a range of text fields which contain last names somewhere in the text(some at beginning of string, some at end of string and some in middle of string). In cell B1, I need to search for a range of names(C1:C4) in A1. If a match is found, then the formula should return the the value of the adjacent cell in column D describing their position on the project.

I can do it using VLookup if all the names are on the left.
=VLOOKUP(LEFT(A1,4),C$1:D$4,2, FALSE)
But it wont work if the text is scattered in the cell.

Thanks,