Partial matching between columns

[Joey1510]

Hi,

I have a list of names of current tenants and I need to find a way to see if any match a list of new tenants (basically where other tenants would move out but one stays the same) - is there anyway to do this in a formula?

Sometimes there is more than one so column B is always set up as 1st tenant AND 2nd Tenant AND 3rd Tenant etc (most is 5) and sometimes column A would have more than one tenant so I need something that would flag up the in the below examples:

Col A Col B
John SMITH AND Ian DAVIES Ian DAVIES AND Jade CONNOR
Gary ANDREWS AND Sam CHARLES AND Sian LLOYD Stuart JAMES AND Sam CHARLES
Steve BROOKS AND Clare CHARLES Steve BROOKS

Thank you

 

[alansidman]

Cannot distinguish your columns. Please upload your data using XL2BB. Look at the link in my signature.

 

[Joey1510]

Is this better?

 

[KP117]

Consider this at C2 and copy down.
=IF(MAX(IFERROR(SEARCH(TRIM(MID(SUBSTITUTE(B2,"AND",REPT(" ",LEN(B2))),1+(COLUMN(A1:F1)-1)*LEN(B2),LEN(B2)))," "&A2),0))>1,"Yes","NO")