Finding a partial match within a list on another tab

**IN_FOR_SIN** · 01-21-2013, 10:19 AM

Hi

I hope someone can help!!!

I have 2 lists of names, List 1 includes the persons "title" (Mr, Mrs, Miss or Ms) in it. List 2 is in another tab and does not include titles. I am trying to work out a formula that will look up the name in List 1 (minus the title), compare it to all names in List 2, and display "found" if the record exists, eg:

List 1 (A1 - A5):
Mr John Beard
Mrs Hanna Wild
Miss Claire Dinner
Ms Kate Shank
Mr Mike Dart

List 2 (in another tab):
Paul Davies
Gemma Jones
Dan Hands
Claire Dinner
Mike Hammond

Result (B1-B5):
Not Found
Not Found
Found
Not Found
Not Found

Thanks in advance

**benishiryo** · 01-21-2013, 10:24 AM

hi there, welcome to the forum. assuming your List 2 is in Sheet2!A1:A5 & List 1 is in Sheet1, try this in Sheet1:
=IF(COUNTIF(Sheet2!$A$1:$A$5,MID(A1,FIND(" ",A1)+1,255))>0,"Found","Not Found")