Intelligent vlookup forumla

Hello,

My colleagues and I are having a few vlookup issues.

The same problem has arisen a number of times in the past couple of weeks.

We have two datasets, both of which contain company data.

We need to "marry" both datasets consequently excluding any duplicate entries. HOWEVER, the company names in either dataset DIFFER.

EG; DS1: Royal Mail Pensions Fund
DS2: Royal Mail Pension Fund

EG2; DS1: 1794 Commodore Fund
DS2: 1794 Commodore Funds

EG3; DS1: ABS Capital Management
DS2: ABS Asset Management


Therefore, when performing a vlookup on a company in DS1 to see if it's in DS2, it doesn't work because it can't recognise its the same company.

I've tried removing the false, and alphabetically sorting the dataset but still doesnt work. I've tried some other vlookup suggestions with limited success.


Am I asking the vlookup to think too much for itself?

Thanks in advance

Originally Posted by npanayotou

... HOWEVER, the company names in either dataset DIFFER....

Have you tried matching on the first four characters only?