Hi there I'm hoping someone here can help me out.
I've got some pretty untidy data and I am looking to do a VLOOKUP / INDEX MATCH sort of function but having no luck as the data is quite untidy.
So basically I've got a list of names and there websites in one sheet.
Then a second sheet with just a list of names that I want to populate with the websites from the other sheet as to cut down the work load.
The problem is the company names are not entered in the correct format (i.e for an abbreviated company spaces are used instead of .'s). Another example is when there is multiple branches of a company.
So worksheet 1 would be
Company Name: Company
Website: www.company.com
Worksheet 2 would be: Company Name : Company (UK) Company Name : Company (USA)
Where both should have the same website as found in worksheet 1 is there any possible way to do this. I have tried VLOOKUP and INDEX/MATCH and set the condition to TRUE but it does not return good results.
Thanks in advance for any advice.
Fuzzy matching is a far from an exact science, particularly when it comes to the built in Excel functions.
If I was to recommend the safest bet - I would say create a de-duped list of the values in sheet 2 and create a matching lookup value for sheet one. This would certainly be the most accurate (but time consuming and depending on how many times you will use these values)
Alternatively, there are 'fuzzy matches' from various sources that can be used. One of the famous ones is the 'Jaro Winkler Distance' : a version of which can be found at this link: http://garonfolo.dk/herbert/2013/07/excel-vba-jaro-winkler-distance-fuzzy-matching/