Hi!
How do I modify so the next column
over in my report will display the score for each individual?
Use a different, less complicated lookup formula:
Assume the original table is in A1:B5
The extracted top 5 names are in the range D1:D5
In E1 enter this formula and copy down:
=VLOOKUP(D1,A$1:B$5,2,0)
Biff
"quailhunter"
wrote in message
...
Biff,
Your code worked like a charm. I used the -(ROW(B$1:B$5)/10^10, and got
exactly what I was looking for... Q. How do I modify so the next column
over in my report will display the score for each individual?
Biff Wrote:
P.S. -
Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???
My formula will return:
Alberts
Smith
Johnson
Able
Reese
If you want to "flip" Smith and Johnson just change this portion of
the
formula in both places:
+(ROW(B$1:B$5)/10^10
Change to:
-(ROW(B$1:B$5)/10^10
Biff
"Biff" wrote in message
...
Hi!
Try this:
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))
Copy down as needed.
Biff
"quailhunter"
wrote in message
...
I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?
I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX
MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.
=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))
Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110
If I change the '1' in the LARGE function to 2, then to 3... It
returns
Alberts, then Johnson, then Johnson again... So, it's only finding
the
first occurrence of Johnson and not returning Smith for '3'...
Is there anyway to code this problem so I'd return Alberts,
Johnson,
Smith???
--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=472479
--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479