VLOOKUP more than one instance

    My first post.
    -----A-----B-----C
    (1) 5280 5,000 1/01/03
    (2) 5280 6,000 2/01/03
    (3) 5280 9,000 5/01/03


    If I use a VLOOKUP for the above data it will return the value of the first found instance of 5280 of 5,000 on 1/01/03 from Row 1.


    I looked in the Excel Help file but found limited advise.


    Is it possible to modify or nest the VLOOKUP to find the second instance and/or even the third? If so, can you provide an example?


    My spreadsheet contains 780 rows involving over 50 different dates but for each item listed in column A there are no more than 4 instances of like individual items.


    Perhaps an "IF" statement nested in the VLOOKUP?


    I suspect it simply can not be done but it sure seems like it should be possible. I do not know how. :no:

    Hi Report_2


    There are a couple of ways and one is via a User Defined Function, see Here


    The other (and perhaps best) is to insert another column into "A" then enter


    =B2&C2&countif($B$2:$B$780,B2)


    This will join the two columns and enter the occurence number after it. You then use the VlookUp as normal using Column "A" as the first Column in the table.

    I have attached the results of adding the formula.


    1085020376673
    Which is item 108, 5020 pcs on 37667 or 2/15/03 and there are 3 occurances.


    It does produce the info but I do not see how to look up and separate that info from and into the target spreadsheet.


    I am Not as fluent in Excel as I would like to be but, I can figure out how to separate the information but I do not see how to use the VLOOKUP to find the second or third result.


    It may be simple but I am not aware.


    I have never tried using a UDF but I will study it's usage. It would appear to be more approriate to my goal.


    Thank you again for the help along with any more that you can offer.


    Additionally I see there are many available resources here to learn what I am wanting. Which would you recommend for this newbie? I have taken the 3 levels of Excel training but they really do not teach the good parts.

    Won't AutoFilter or Advanced Filter achieve this?


    To attach a file (preferably an example of your workbook) use the browse option to find your file from byour computer.

    I'd go with Roy on this one... advanced filter will allow you to specify your criteria and have it bring back all instances, without any concatenations..... maybe one downside is that you need to "refresh" the query, as oppose to a formula which will bring back the info automatically

    I modified the User Defined function and it works like a charm.
    =FindNth($A$2:$C$6,A2,1,2) to get the first and so on.
    I can replace the range with the other spreadsheet as part of the range.


    Is there a way to make this user defined function always available in any workbook rather than just within the single workbook?



    Function FindNth(Table As Range, Val1 As Variant, Val1Occrnce As Integer, _
    ResultCol As Integer)


    '''''''''''''''''''''''''''''''''''''''
    'Written by OzGrid Business Applications
    'http://www.ozgrid.com


    'Finds the N'th value in the first Column of a table that has a stated _
    value on the same row in another Column.
    '''''''''''''''''''''''''''''''''''''''


    Dim i As Integer
    Dim iCount As Integer
    Dim rCol As Range


    For i = 1 To Table.Rows.Count
    If Table.Cells(i, 1) = Val1 Then
    iCount = iCount + 1
    End If


    If iCount = Val1Occrnce Then
    FindNth = Table.Cells(i, ResultCol)
    Exit For
    End If
    Next i
    End Function


    Thank you so very much! :lol:

    I often use the Auto Filter function and find it useful when I do. I had never tried using the Advanced filter until this morning but I find it to be exactly the same as using Auto Filter.


    Using either will duplicate the information in rows and columns as originally displayed. My goal was/is to copy the info from the columns and paste them into the same row (as shown in my attached image in the previous post) whereas the filter functions will not.


    I appreciate the input just the same.


    I am quite confident that I will be able to use the User Defined module to accomplish my task.


    Thank you.


    I would still like to know if Excel2K can access the user defined function like Excel97 can within the Paste Function dialog box.


    Alternately, if 'User Defined' is not available in Excel2K can it be found under another title?


    Anyone with Excel2K know off hand?

    Just to follow up:
    "I would still like to know if Excel2K can access the user defined function like Excel97 can within the Paste Function dialog box."


    It does show up.


    Thanks again to all.

    Dave, I would like to thank you for opening this powerful door to me.


    'Macros are much more than simply recording key strokes'


    I would like more input. Which package would you recommend to a newbie to expand this new frontier of knowledge?

    Hi Report_2


    You have been bitten by the VBA bug :o


    RE: Which package would you recommend to a newbie to expand this new frontier of knowledge?


    What do you mean by "package" :question: If you mean which Excel version, then I would opt for Excel 2002, Excel 2000 was not much different than Excel 97 and not worth the upgrade cost in my opinion.


    If you mean training, feel free to contact me privately anytime at [email protected]. It is 20:00 here in Oz and "The Bill" is about to start, but I will be back online in the morning.


    :thumbup:

    oh dear... apparently "The Bill" is the #1 prime time TV show in Oz, I'd forgotten about that till you just mentioned it


    mind you, the UK was "Neighbours"-crazy for a decade till Jane upped and left


    :D

    Re: VLOOKUP more than one instance


    I have an issue.. NumberRole 4 IT Senior Manager4 IT Senior Manager4 IT Risk Officer4 IT Developer4
    BU System Owner





    EONId474750965178

    What I am looking for is "I want run a query in table 2-column 1 matching with the Eon ID with number-4(column 1) of the first table and if it matches then i should be able to check whether for the number, is there a role named as " IT Senior Manager" or "IT Risk Officer" in column 2 of table 1. If the roles are there then the output should be "IT senior Manger" (if it is there otherwise) "IT Risk Officer".

    Can i do thuis using vlookup and an IF condition?
    for example I tried:
    =IF(VLOOKUP(A2,userrole,2,FALSE)="IT Senior Manager",TRUE,FALSE)

    I get the output as true
    problem with this is it only reads upto the first instance of number 4 and doesnt go down till the number 4 ends.
    How do i get that going?

    Suggestions are highly appreciated.

    Thanks,
    Jigi

    Re: VLOOKUP more than one instance


    Sorry for the above messed up tables.
    The above tables are as follows
    Table 1:
    number role
    4 IT Senior Manager
    4 IT Senior Manager
    4 IT Risk Officer
    4 IT Developer
    4 BU system Owner

    Table 2:
    Eon Id
    4
    747
    5096
    5178

    Re: VLOOKUP more than one instance


    jigi,



    It is against Forum Rules to hijaack another thread. Start your own thread and, in need be, link back to this thread as a reference if it help to clarify your own question/need.


    I understand you are new the the forum, however, the rules are clearly defined and you agreed to follow them. Please take the time to read and understand them so that your posting privileges are not interrupted by locked threads. Thanks,


    AAE

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login