Values are already exists in other worksheets

When you try comparing two fields that may not be the same it becomes very difficult, Im sure theres algorythms out there that will predict the likelihood of a match but they themselves would have to be fairly complex.

For instance, a great one to use as an example is that Great British tradition the BBC

In numerous places you will find this referred to as:
BBC, B.B.C, British Broadcasting, British Broadcasting Corp, British Broadcasting Corp. ,British Broadcasting Corporation, The Beeb, The BBC etc etc

You could do things such as removing punctuation which would resolve the 1st 2 or doing a Replace to change "Corporation to Corp", "Limited to Ltd", Removing "The" etc but comparing BBC in one sheet to "British Broadcasting Corporation" in another is never going to match up.