Identifying approximate text values

[rita08mustang]

Hello all,

I have a list of 100 company names (List A), and I have a second, longer list of company names (List B). There are several company names that these two lists have in common, but the actual text differs slightly. I am trying to see if there is a way to see what names in List A appear in List B knowing that the company name in List B may differ slightly than List A.

An example of this would be "Jones Lang Lasalle" vs. "Jones Lang Lasalle Inc."

Another example would be "Allstate" vs. "The Allstate Corporation"


Thanks in advance for your feedback and advice. First Post

 

[LockeGarmin]

There isn't going to be a silver bullet on how to approach this, but here is my attempt. This will evaluate TRUE for both cases you put (if you put them in A1 and B1), but it would find "Jone Lang Lasalle" and "Jones Lang Lasalle" (The first Jones missing an 's') as FALSE.

=IF(MAX(LEN(A1),LEN(B1))>0,ISNUMBER(IF(LEN(B1)>LEN(A1),SEARCH(A1,B1),SEARCH(B1,A1))),FALSE)

 

[Eric W]

What you're asking for is a common problem, and there are lots of attempted solutions. Try searching for "Fuzzy lookup" in these forums or on Google. Since each situation is different, you may have to look at several solutions to find one that works for you. Here are 2 options:

https://www.microsoft.com/en-gb/download/details.aspx?id=15011

http://www.mrexcel.com/forum/excel-...-version-plus-explanation-28.html#post3145162

The first is a free add-in from Microsoft. The second is a set of functions developed by someone on this forum. (See post #275 for the code, and 276 and 277 for some examples.) I haven't tried them, but they seem fairly robust.

Good luck!