How to compare two lists of text and provide a matching level in percentage

[cga4200]

Hello all,

I wish to compare a list of text (Name to Match) to a static list of text (Standard Name) and pull the best match from the static list along with a matching value in percentage.

The values under the Standard Name column are static values. The values under Name to Match are string values for which I would like to find the best match from Standard name column. For instance if under column Name to Match, I have Coca-Cola Company, the code should pull The Coca-Cola Company as the best match and provide an matching level of, let's say, 95%. The accuracy levels on the table an approximation of I would like the code to do.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Standard Name[/TD]
[TD][/TD]
[TD]Name to Match[/TD]
[TD]Matching Standard Name[/TD]
[TD]Accuracy[/TD]
[/TR]
[TR]
[TD]The Coca-Cola Company[/TD]
[TD][/TD]
[TD]Gap[/TD]
[TD]The Gap Inc.[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]The Gap Inc.[/TD]
[TD][/TD]
[TD]Coca-Cola Company[/TD]
[TD]The Coca-Cola Company[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]Bank of America[/TD]
[TD][/TD]
[TD]Coca-Cola Financial[/TD]
[TD]The Coca-Cola Company[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Countrywide Financial Corporation[/TD]
[TD][/TD]
[TD]Contrywide[/TD]
[TD]Countrywide Financial Corporation[/TD]
[TD]70%[/TD]
[/TR]
[TR]
[TD]AOL LLC[/TD]
[TD][/TD]
[TD]AOL LLC[/TD]
[TD]AOL LLC[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Six Flags Inc[/TD]
[TD][/TD]
[TD]Tennis Supplies[/TD]
[TD]No Match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Northrop Gurman Corp.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried using a VLOOKUP with the [range_lookup] parameter set to TRUE-Approximate Match, but it does not yield the desired results. I would like to use VBA to solve this problem, would somebody please point me in the right direction?

Thank you
AC

 

[VoG]

Try a fuzzy lookup http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html