Approximate Matching or lookup in Excel

[G.Bregvadze]

Hi,

I always this forum when I have some difficulties with functions, recently, many of the forum moderators helped me with many difficulties.

So I hope that this also won't be a problem.

I am a moderate user of the excel and use Excel 2010, sometimes 2013.

My case:

I have a list of dozen of names of people and organizations with their amounts of fee.

Next I have another file containing those organizations and people with their names. I need to lookup a specific name in one list and match with another name in another list.

The problem is that one list is done by another person and second by other person, so each name is different. Examples: Nike LTD - LTD Nike, Nike - Nike LTD, John Davidson - Davison John.

There are even spelling mistakes: John Davidson - Davidson.


As you can see a typical vlookup function can not be used.

is there is any other function which I am not aware of or is there any combination of functions which will match these type of names to each other? please help.

[Portuga]

I would sugest you clean up your data and allow only 1 valid entry per name/organization.

You could create a list of the clients and then use that list as validation for that field, using a "named range/offset validation list" approach.
Only those values would be accepted in that field and you would find that everything else would be a lot simpler to achieve.

If you are allowing different names and even spelling mistakes on your data, it is a recepe for continuous errors.

[G.Bregvadze]

Yeah, you are right and for the future, the data must be entered after validation, but now I could not change this...

IF anyone could help with an approximate matching I would be grateful

[Portuga]

You can always change this. Use the "Find + Replace" to, well, do what the name says. Replace All entries to the names you want and then use the validation.

Besides, how do you define "approximate matching" .
There are ways to do it, but you would have to specify excatly what constitutes a matching value?
Is it John, is it Davidson? How do you define for the remaining customers?

The work you would have to do it, is probably more than if you clean up your data and use validation.