Excel Formula for Matching

[marcwithak629]

I need a new and improved formula to match homeowners to other information I am able to obtain. I get homeowner data from public websites (e.g. online State registration records). I am also able to obtain some or all of the following family information: father's first and last name, mother's first and last name, and/or family name only "the Smith family."

I need a formula that matches as much info as possible in one cell (homeowner information) to as much info as possible in another cell (my names data) ... to identify the BEST match.  In other words, if I have both parents' first names and their last names (including the mom's maiden name), I assume that I can get a better match than if I only have a common last name that might match 5-20 homeowners in the area. The formula needs to match as much as possible or as little as is available (e.g. sometimes, I only have the family's last name).

The most recent formula, courtesy of a great person in this community a few years ago, is below. It's a GREAT formula; I'm simply hoping to use more data to get stronger matches.

"HamzaQ" is the file name and "WorkingList" is the worksheet. This formula comes from a cell in the master spreadsheet with my database of names. 

=LET(in, [HamzaQ.xlsx]WorkingList!$B$2:$F$85948,
key, $G3,
inC, INDEX(in, , 1),
inCloc, IFERROR(SEARCH(key,inC),-1),
keyfinds, TRIM(SWITCH(inCloc,-1,FALSE,1,LEFT(inC,LEN(key)+1),MID(inC,inCloc-1,LEN(key)+2))),
pass, IFS(key=keyfinds,TRUE, (key&",")=keyfinds,TRUE,TRUE,FALSE),
fset, FILTER(in,pass,0),
out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)),
out
)

[#2]

@marcwithak629 

=LET(
    in, [HamzaQ.xlsx]WorkingList!$B$2:$G$85948,
    key, $G3,
    inF, INDEX(in, , 2),
    inM, INDEX(in, , 3),
    inFLoc, IFERROR(SEARCH(key,inF),-1),
    inMLoc, IFERROR(SEARCH(key,inM),-1),
    keyFindsF, TRIM(SWITCH(inFLoc,-1,FALSE,1,LEFT(inF,LEN(key)+1),MID(inF,inFLoc-1,LEN(key)+2))),
    keyFindsM, TRIM(SWITCH(inMLoc,-1,FALSE,1,LEFT(inM,LEN(key)+1),MID(inM,inMLoc-1,LEN(key)+2))),
    passF, IFS(key=keyFindsF,TRUE, (key&",")=keyFindsF,TRUE,TRUE,FALSE),
    passM, IFS(key=keyFindsM,TRUE, (key&",")=keyFindsM,TRUE,TRUE,FALSE),
    fsetF, FILTER(in, passF, 0),
    fsetM, FILTER(in, passM, 0),
    outF, IFERROR(INDEX(fsetF,2,), INDEX(fsetF,1,)),
    outM, IFERROR(INDEX(fsetM,2,), INDEX(fsetM,1,)),
    out, IF(outF<>"", outF, IF(outM<>"", outM, ""))
)

Since no one has responded to this for over a day or more, I am sending the attached formula that I regenerated with the help of the AI.

I haven't been able to try this out, so please save your file in advance so as not to evaluate any errors in the original file.

Try the formula, maybe it will help you further with your plans. If not...please just ignore it.

But I still hope it helps.