Matching two columns based on partial text match

[Pika735]

I have a sheet with two columns of names and I need to see if one column has partial matches. For Example:

John Smith	JOHN H SMITH	FALSE (But should be TRUE)
Reba Jones	SAMANTHA ROGERS	FALSE
Taylor Johnson	ANNA JOHNSON	FALSE (But should be TRUE)

In this case, I need to know if surnames match or full names match but have an initial
I have tried If(Isnumber(Search but wasn't able to get it to work. Any ideas?

 

[sspatriots]

I had a similar issue once. I used the sample file in the link below to make it work for my situation.

VLOOKUP Fuzzy Match

 

[sspatriots]

Also, there is an "Add-In" download that Microsoft has adopted for Excel in the link below. This will give you the percent match between 2 columns of names. I found this one particularly useful for situations like you are describing.

Fuzzy Lookup

 

[Peter_SSs]

I need to know if surnames match or full names match but have an initial

Perhaps I am not grasping the requirement but surely the red check is not needed? After all, if the full names match with/without initial then the surnames must be matching so the blue test would be sufficient.

23 05 05.xlsm
	A	B	C
1	John Smith	JOHN H SMITH	TRUE
2	Reba Jones	SAMANTHA ROGERS	FALSE
3	Taylor Johnson	ANNA JOHNSON	TRUE
Match

Cell Formulas
Range	Formula
C1:C3	C1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))=TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" ",50)),50))

 

[sspatriots]

Are these only for partial matches in the same row of column B or did you want to search the entire column B for any same row cell value in column A?

 

[sspatriots]

Surely if all surname matches are "TRUE", then any full name with a middle initial will be "TRUE" per your example. If that is all you need and your range or data is A1:B3, then you could past the formula below in A3 and copy it down.

=IF(RIGHT(A1,FIND("/", SUBSTITUTE(A1," ","/", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))=RIGHT(B1,LEN(B1)-FIND("/", SUBSTITUTE(B1," ","/", LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))),"TRUE","FALSE")

 

[alansidman]

An alternative is to load each table/range to Power Query Editor. Your example
Each column is its own table

Book1
	A	B	C	D	E
1	Column1	Column1		Column1	Table2.Column1
2	John Smith	JOHN H SMITH		JOHN SMITH	JOHN H SMITH
3	Reba Jones	SAMANTHA ROGERS		REBA JONES
4	Taylor Johnson	ANNA JOHNSON		TAYLOR JOHNSON	ANNA JOHNSON
Sheet1

First Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Uppercased Text" = Table.TransformColumns(Source,{{"Column1", Text.Upper, type text}})
in
    #"Uppercased Text"

Second Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

Join the two queries in a fuzzy match

let
    Source = Table.FuzzyNestedJoin(Table1, {"Column1"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=.03]),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1"}, {"Table2.Column1"})
in
    #"Expanded Table2"

 

[Peter_SSs]

Surely if all surname matches are "TRUE", then any full name with a middle initial will be "TRUE" per your example. If that is all you need and your range or data is A1:B3, then you could past the formula below in A3 and copy it down.

=IF(RIGHT(A1,FIND("/", SUBSTITUTE(A1," ","/", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))=RIGHT(B1,LEN(B1)-FIND("/", SUBSTITUTE(B1," ","/", LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))),"TRUE","FALSE")

Just wondering if that formula does something that the considerably simpler formula in post #4 doesn't do?

 

[sspatriots]

I did not see your formula when I went to post my response or I wouldn't have wasted the time on it. I just had something handy that I had done before that was very similar.