Possible Match

[roel1009]

What's the formula I need to put in Column B to return a fuzzy lookup of Column A from Column C. In this case all names in Column C will be the result.

A	B	C
113 Doughty Blvd LLC & AIT Credit Servic		113 Doughty Blvd LLC and AIT Credit Services Inc
1256 Realty, LLC		1256 Realty LLC
128 Nostrand Avenue LLC		128 NOSTRAND AVENUE LLC and 817 UTICA AVENUE LLC
18 Ave. Group Inc.		18 Ave. Group Inc
Deserine Y. & Hopeton Bartley		Deserine Y Bartley

 

[GraH]

For these kind of situation, I would always revert to the fuzzy match options in Power Query.

Default Threshold is 0.80, this is a balance between close matching and cartesian product matching (all with all). 1.00 is an exact match, 0 being a match of all values to all values.

The Query M-Code:

let
    Source = Table.FuzzyNestedJoin(Values, {"Values"}, Compare, {"Compare To"}, "Compare", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.50]),
    #"Expanded Compare" = Table.ExpandTableColumn(Source, "Compare", {"Compare To"}, {"Compare To"})
in
    #"Expanded Compare"

Your data:

Book1
	D	E	F
4	Values		Compare To
5	113 Doughty Blvd LLC & AIT Credit Servic		113 Doughty Blvd LLC and AIT Credit Services Inc
6	1256 Realty, LLC		1256 Realty LLC
7	128 Nostrand Avenue LLC		128 NOSTRAND AVENUE LLC and 817 UTICA AVENUE LLC
8	18 Ave. Group Inc.		18 Ave. Group Inc
9	Deserine Y. & Hopeton Bartley		Deserine Y Bartley
Sheet1

Result of query:

Book1
	A	B
1	Values	Compare To
2	113 Doughty Blvd LLC & AIT Credit Servic	113 Doughty Blvd LLC and AIT Credit Services Inc
3	1256 Realty, LLC	1256 Realty LLC
4	128 Nostrand Avenue LLC	128 NOSTRAND AVENUE LLC and 817 UTICA AVENUE LLC
5	18 Ave. Group Inc.	18 Ave. Group Inc
6	Deserine Y. & Hopeton Bartley	Deserine Y Bartley
Merge1

EDIT: and it's UI driven, as the options are in the helper screen when doing a merge operation: