Lookup with partial text match

So I'm working on a tracker which needs to display the latest date an employee has signed in to work. I'm able to get the date using:

Please Login or Register  to view this content.

The issue I'm having is that the "Text String" needs to be an EXACT match, whereas I need to have a partial match. A1:A1070 references a linked SharePoint list in a worksheet, and the column contains the employees name, but also their office symbols and such, which is subject to change. And C1:C1070 obviously references the corresponding date.

Basically I want to be able to search for "Smith, John" in a field which may look like "Smith, John AFGSC 11 BS/DOA".

Thanks!

Edit: It's worth noting that the worksheet where I want to display this formula has a column (A) of the employee's names in the "Smith, John" format.

I've tried =LOOKUP(2,1/('Go NoGo Data'!A1:A1070="*"&$A$2&"*"),'Go NoGo Data'!C1:C1070)

From what I understand wildcards don't work in the formula I'm using, so I need some other method.

Perhaps:

Formula:

Please Login or Register  to view this content.

Here's another possibility if the name is always at the start of the string.

=LOOKUP(2,1/(LEFT('Go NoGo Data'!A1:A1070,LEN($A$2))=$A$2),'Go NoGo Data'!C1:C1070)

You're welcome. Thanks for the feedback!