Results 1 to 7 of 7
Thread: vlook up problem (excel 2000)

20070519, 01:59 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 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?

20070519, 03:19 #2
 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> Ricky

20070519, 09:20 #3
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 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

20070519, 09:50 #4
 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

20070519, 10:46 #5
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 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

20070519, 12:33 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: vlook up problem (excel 2000)
ROW is a builtin 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.

20070519, 13:38 #7
 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