Hi Haseeb, Thanks for you help on this query but recently I noticed onething, this formula works when we have to match strings from left. In this case it works fine. But when I have the below scenario, it doesnt work properly.
For example, in source sheet I have "Analex Corporation" and in Destination I have "The Analex Corp" alongwith other values starting with "Anal". The best match for source string would be "The Analex Corp" but since there is "The" at the left it's not picking it up. Can you please suggest me any solution for this. Hope you get what I am trying to say. Thanks, Anish On Tue, Jul 12, 2011 at 8:25 AM, Haseeb Avarakkan < haseeb.avarak...@gmail.com> wrote: > Hello, Try this Array Formula. Must hit CONTROL+SHIFT+ENTER, rather than > ENTER. > > > =INDEX(Sheet1!A:A,MAX(IFERROR(MATCH(LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&"*",Sheet1!A:A,0),0))) > > Copy down. See the attached. > > HTH > Haseeb > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
