Formula to find and replace where needed

Hi, I have a table of data and it includes names in Column A. Sometimes the same people's names are entered a little differently but it is the same person. There are about 10 names where there is a variation in spelling etc. The thing is that in most cases the main letters are together e.g. you may find (dan able, d. able, mr d. able). The same type of thing happens with some other names. I thought of doing a find and replace for *able* and a similar things for others like *don*. There is one name with a comma (,) in the middle (e.g. Mc Dona,ld and Mc Donald and McDonald) but I can replace that manually ahead of time if it complicates things. I want to use one formula or a macro to finds those names in the column and replace the name in the column with one of those in the list if it has the name within it.

I have attached a spreadsheet to make it clearer.

I am also copying and pasting the info here in case that's easier for reference:
BEFORE
tracy
tracy's
dan able
d. able
d. able
McDona,ld
McDonald
Mc Donald
Curtis
Wadsworth


REFERENCE LIST OF NAMES TO CHANGE TO WHERE THERE ARE MANY VARIATIONS:
tracy
able
McDonald

RESULT after doing the find and replace formula
tracy
tracy
able
able
able
McDonald (I can change this manually first if McDonald with the spaces and , make it difficult to set up the formula).
McDonald
McDonald
Curtis
Wadsworth

Thanks!

hi maymano. i'm not sure if comma is something you always want to remove? if you want to manually check & remove, then do it first before you do:
=IFERROR(LOOKUP(2*15,SEARCH($C$2:$C$4,A2),$C$2:$C$4),A2)

if you can don't mind replacing it straight away, then:
=IFERROR(LOOKUP(2*15,SEARCH($C$2:$C$4,SUBSTITUTE(A2,",","")),$C$2:$C$4),A2)

you're welcome. yes you can slot that inside, after or before substituting the commas:
=IFERROR(LOOKUP(2*15,SEARCH($C$2:$C$4,SUBSTITUTE(SUBSTITUTE(A2,",","")," ","")),$C$2:$C$4),A2)

=IFERROR(LOOKUP(2*15,SEARCH($C$2:$C$4,SUBSTITUTE(SUBSTITUTE(A2," ",""),",","")),$C$2:$C$4),A2)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.