Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    vlook up problem (excel 2000)

    How do I get this list to show all the different names associated with the ranking of 61 instead of continually reporting the first name associated with the 61st ranking?
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlook up problem (excel 2000)

    Bill - The sample you attached did not have any formulas, so it was a bit difficult to see what you were attempting to do. I've sent your sheet back to you with some data on Sheet2 along with an example of Auto Filter. Perhaps that will do what you had in mind.

    In regards to vlookup... by design, it will return the results from the 1st match it finds in a list.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files
    - Ricky

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vlook up problem (excel 2000)

    Thanks for the assist...
    So what you are saying is that the vlook up formula will only report the first name that matches the 61st ranking and not any others that match with in the range? Is there any other modification / filter we can couple with the vlook up formula that will check and list the next person in line? if not...

    I will try to incorporate the filter process to get the final desired results.

    Appreciate the work.
    cvbs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlook up problem (excel 2000)

    I am not sure exactly what you are after, but if you want to lookup multiple items using a lookup you will have to create your own functions. I have done so in the post Re: Lookup more than one row (2000) which may be what you are after...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vlook up problem (excel 2000)

    Hey Steve,

    Thanks for this insight here. What I am trying to get is each person who ranked a 61 to be listed on a seperate row. In the database I have there are 15 different people who rank a 61. I am trying to list the first 6 and Vlookup formula is only returning the first one it finds 6 times. As I looked at the group you have put together I believe this one might answer the problem. What do you think?

    looking If you want to put them down a row you could enter something in A1 something like:


    =VLIndex("a",$A$1:$A$52, 5,row())
    and copy it down the row and you will get the list.

    ... just one question can you explaing the brackets after row? Is there something that is suppose to be in between?

    Please let me know your thoughts.

    Thank you again.
    cvbs

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlook up problem (excel 2000)

    ROW is a built-in Excel function: =ROW() returns the row number of the cell containing the formula. All functions must be followed by brackets ( and ). If the function takes one or more arguments, they go between the brackets, but if the function doesn't have arguments, the name must still be followed by brackets ().
    Other examples are =TODAY() which returns the current date and =PI() which returns the number 3.141592... that plays a role in mathematics.

    Note: the function ROW can also take an argument: =ROW(A5) returns the row number of cell A5, i.e. 5.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlook up problem (excel 2000)

    Steve,

    Very cool UDF. I wish I wouldn't have mssed it the first time by. It is now in my bag of tricks... <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •