Lookup function that finds text within a cell against a list

[pdenna1]

We have a master list of the name of all our clients and we receive new lists of attendees of a trade show that has the company names listed as they would list it, but that often doesn't match our master list.
I want to be able to match a cell which has a word in it that is in another cell in a list.

[TABLE="width: 500"]
<tbody>[TR]
[TD]New list[/TD]
[TD]Master list[/TD]
[/TR]
[TR]
[TD]The Cooper Co's[/TD]
[TD]The Cooper Companies[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The master list is a huge list and because the cells don't match perfectly a vlookup isn't able to find The Cooper Co's in the master list. I want to output the name in the master list using the name in the new list. I've used the wildcard before and after, but it isn't able to find it still.

If the formula could find any words that match both cells that would be excellent. Thanks.

 

[mikerickson]

Which direction is the problem:
Given "The Cooper Co's" find "The Cooper Companies"
or
Given "The Cooper Companies" find "The Cooper Co's"

Or (because of the varieties of names) both directions of that example might be needed?

Have you done a search on this forums' posts for Fuzzy Match ?

 

[pdenna1]

Given "The Cooper Co's" find "The Cooper Companies".

I always have the master list that i'm comparing everything too. I want the master list name as the output.

I've looked up the fuzzy match forum, but i'm hoping for a more simple answer before I start running macros.