Match the address between 2 cells

[Deepakchoudhary]

My data is in column D2:D192 and column K2:K192, each COLUMN having addresses of same site in same row, but text written unorganized way... can say Building name, Location name etc written in different order in both columns in same row. What can be the formula to find if few words common in both cells/columns...

 

[JamesCanale]

Here is something I had lying about - column M shows the percentagey match. COlumn N and O have things to remove from consideration.

Fuzzy Match.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
1	350 fifth avenue empire state building New York							Empire State Building		68.8%	.	the
2	1600 Pennsylvania Avenue							The White House		0.0%	,	of
3	100 Main Street Building 6							Building 6 Main Street		90.0%	?	and
4											!	to
5												in
6												is
7												you
8												a
9												that
10												it
Soft match

Cell Formulas
Range	Formula
M1:M3	M1	=LET(x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,$N$1,""),$N$2,""),$N$3,""),$N$4,""),y,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K1,$N$1,""),$N$2,""),$N$3,""),$N$4,""),z,$O$1:$O$10,a,TEXTSPLIT(x,," "),b,TEXTSPLIT(y,," "),aa,FILTER(a,ISNA(XMATCH(a,z))),bb,FILTER(b,ISNA(XMATCH(b,z))),c,SUM(ISNUMBER(SEARCH(a,TEXTJOIN(" ",TRUE,bb)))+0)/ROWS(aa),d,SUM(ISNUMBER(SEARCH(b,TEXTJOIN(" ",TRUE,aa)))+0)/ROWS(bb),AVERAGE(c,d))