It becomes decidedly more difficult. A measure of success might be
achieveable using fuzzier search terms within the criteria eg: ...
ISNUMBER(SEARCH(...))

Lets say you have in Sheet2's A1:B1,
data such as: Anna Rose, Mary
(Mary in B1)

And in Sheet1,
you have the lookups
in A1: Anna, in B1: Mary

This revision of the earlier, placed in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(I SNUMBER(SEARCH(TRIM(A1)&"
",Sheet2!A$1:A$100))*(Sheet2!B$1:B$100=B1),),0))," Y",""))
would return a correct: "Y" for the data in Sheet2. It will avoid returning
a spurious "Y" should you have: Annabel, Mary in Sheet2 (instead of: Anna
Rose, Mary).

Above is still not watertight of course, but the method might help you to
narrow down the desired searches.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Christie" wrote:
Thank you this helped a great deal however is their a way that I can include
in the formula that if one sheet has a christian name, eg Anna and the other
sheet has both christian and middle name, eg Anna Rose that this will show as
a match? Also to include this in matching address's???