Formula or VBA for lookup

[baigm]

Hi All,

I have a unique situation. i need to find names of 100 from another sheet of 10000+
the issue is the name is not exactly matching in the sheets. (either first name or last name will match) - i tried concatenate but not working.

I need either a formula or a VBA script - when run i should get the similar names in a different column.
below is the sample
Please help

Regards
Baigm

Sheet one names	Sheet 2 Names
ABCD LLC LIMITED	ABCD LIMITED
**Mr*XYZ*Limited*	XYZ
Mr. KKS XY	XY KKS

 

[sprigelz]

You could try using a nested Substitute formula. Though this can get very confusing, depending on how many variable you want to add, it's a functional and fairly simple formula.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"LLC",""),"*",""),"MR",""),".",""))

 

[baigm]

Hi Sprigelz - thanks but this after this also when i tried lookup its not working. and i dont have LLC or Mr only, i have other extra words as well.

 

[sprigelz]

VBA would likely be your best bet in this situation, but you would need to put together a full list of the words/abbreviations that you wanted to trim from the range.