Find the Matching Erp No for the Given Names

[fareedexcel]

Dear Experts,

I have a list of staff names where I need to find their ERP No from the source sheet. Both the sheets does not have the names matching properly. For eg: In the required sheet the name showing as JOHN DAVID KING but in the source sheet the name is JOHN DAVID or JOHN KING.

I tried the following ways to capture the ERP#,

• By splitting the full name in columns and concatenating 1st & 2nd, 1st & 3rd and again using the vlookup formula to capture the erp no from source sheet.
• Also using vlookup with wild card(*) to find the erp no.

Apart from this, is there any way I can extract the erp no with macro or any easy form

 

[Deepak]

http://www.k2e.com/tech-update/tips/431-tip-fuzzy-lookups-in-excel

 

[fareedexcel]

http://www.k2e.com/tech-update/tips/431-tip-fuzzy-lookups-in-excel

thanks for the info

 

[bosco_yip]

Try,

In C2, formula copied down :

=IFERROR(LOOKUP(9^9,SEARCH('Source Sheet'!A$2:A$359,A2),'Source Sheet'!B$2:B$359),"")

Regards
Bosco