You could use a combination of Haseeb's formula and this array formula =INDEX(Sheet1!$A:$A,MIN(IFERROR(MATCH(RIGHT(A2,ABS(LEN(A2)+1-ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)))))&"*",Sheet1!$A:$A,0),"")))
The trick would be to identify *when* you would use either... Sam Mathai Chacko (GL) On Wed, Sep 21, 2011 at 5:24 PM, jmothilal <gjmothi...@gmail.com> wrote: > *Dear friend, > > > =VLOOKUP("*"&A1&"*",Sheet1!A:A,1,0) > > please help me after vlookup "*",&a1&"*" > > Thanks with > > Mothilal > > * > > On Tue, Jul 12, 2011 at 7:31 PM, Anish Shrivastava <anish....@gmail.com>wrote: > >> Thanks Ankur, Rajan, Haseeb and Ashish for helping me.. :) >> >> >> On Tue, Jul 12, 2011 at 5:37 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote: >> >>> *=VLOOKUP("*"&A1&"*",Sheet1!A:A,1,0)* >>> >>> *Use this* >>> >>> * * >>> >>> *From:* excel-macros@googlegroups.com [mailto: >>> excel-macros@googlegroups.com] *On Behalf Of *Anish Shrivastava >>> *Sent:* Monday, July 11, 2011 3:43 PM >>> *To:* excel-macros@googlegroups.com >>> *Subject:* $$Excel-Macros$$ Need help on String Matching**** >>> >>> ** ** >>> >>> Hello Experts,**** >>> >>> **** >>> >>> I am writing this mail with a great hope.**** >>> >>> **** >>> >>> Please see the attached file where I have 2 sheets with data populated >>> column "A".**** >>> >>> **** >>> >>> I want to find the best possible match in Column "B" on 2nd Sheet From >>> Sheet 1.**** >>> >>> **** >>> >>> Since the names are not exactly matching I need your help otherwise a >>> simple vlookup formula would have done the task for me.**** >>> >>> **** >>> >>> Just to simplify my query and for a better understanding I would put the >>> formula which I would have used if the naming convention on both the sheets >>> were same.**** >>> >>> **** >>> >>> =VLOOKUP(A1,Sheet1!$A:$A,1,FALSE) in (cell B1 - Sheet 2)**** >>> >>> **** >>> >>> It will help me in reducing the manual work and also I would learn it..* >>> *** >>> >>> **** >>> >>> Thanks,**** >>> >>> Anish**** >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> 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 >>> >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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 >> > > > > -- > J.Mohilal > Universal Computer Systems > # 16, Brindavan Complex > Otteri, Vellore-2 > > -- > > ---------------------------------------------------------------------------------- > 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 > -- Sam Mathai Chacko -- ---------------------------------------------------------------------------------- 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