Partial Match to Master list

[srizki]

I am trying to standardize a list of names of projects, technicians write different names for the same project for example, on one place it says, Forest Creek Wind farm, on another place it is just Forest creek, etc.
that causes difficulty using formula.



I created a master list of names and using formulas like,




<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=95 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=95 height=17>,=LOOKUP(9.99999999999999E+307,SEARCH([Book1]Sheet1!$C$2:$C$35,A171),[Book1]Sheet1!$C$2:$C$35)</TD></TR></TBODY></TABLE>

or with some little adjustment.

<TABLE style="WIDTH: 67pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=89 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=89 height=17 x:str="'=LOOKUP(9.99999999999999E+307,SEARCH(A171,[Book1]Sheet1!$C$2:$C$35),[Book1]Sheet1!$C$2:$C$35)">=LOOKUP(9.99999999999999E+307,SEARCH(A171,[Book1]Sheet1!$C$2:$C$35),[Book1]Sheet1!$C$2:$C$35)</TD></TR></TBODY></TABLE>

to get the correct name from the master list and copy and paste special on the wrong list. but most of the times this formula is not working. Is there another formula that I can use. Where the formula find first word (most of the times the first word is the same) and bring the correct name from the master list.

So really it should match a partial name to master list.


Thanks