'VLOOKUP PROBLEM WITH MULTI ITEM OR VALUE 'WORK FINE FOR DISTINCT ID/VALUE Suppose we have client id along with purchase id,such as,if we have multiple of client id
Then applying vlookup will not give required result. Client ID Purchase ID uw001 cd001 uw002 cd002 uw003 cd003 uw004 cd004 uw005 cd005 uw006 cd006 uw007 cd007 uw008 cd008 uw009 cd009 uw001 cd00kl uw011 cd011 uw012 cd012 uw013 cd013 uw014 cd014 uw021 cd021 uw021 cd021 uw017 cd017 uw018 cd018 uw019 cd019 uw020 cd020 uw021 cd021 Original table Client ID Purchase ID uw001 cd001 uw002 cd002 uw003 cd003 uw004 cd004 uw005 cd005 uw006 cd006 uw007 cd007 uw008 cd008 uw009 cd009 uw001 cd001 uw011 cd011 uw012 cd012 uw013 cd013 uw014 cd014 uw021 cd021 uw021 cd021 uw017 cd017 uw018 cd018 uw019 cd019 uw020 cd020 uw021 cd021 Table after vlookup in another sheet,That is because vlookup work fine with Distinct value or id. ''''Try this code '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Option Explicit Sub multi_vlookup() Dim MyLookup As String Dim i As Integer i = 1 MyLookup = InputBox("What do you wish to find?", "Enter Data to Find", "Enter data here") Do Until Range("A" & i).Value = "" If Range("A" & i).Value = MyLookup Then MsgBox Range("B" & i).Value, vbOKOnly, "Data Found At :" & i End If i = i + 1 Loop End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Hope It Will Help Happy to Help :) Shyam On Thu, Mar 4, 2010 at 9:54 AM, Gaurav <laman...@gmail.com> wrote: > Hello, > > I have tried this for sometime now but maybe i'm missing something. > I'm trying to use VLOOKUP and the range that i'm searching in could > have multiple occurences of the lookup value. I want to get all the > instances of the lookup value. I have tried to use array formulas with > VLOOKUP and MATCH but nothing seems to have helped with this. After > getting this done I would want to implement the solution through VBA. > Any help with this is greatly appreciated. > > Thanks for the help in advance. > > Gaurav!!!!!!!! > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 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 > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe