Hi guys.
I've been trying to make this feature for a few days now and I still
can't come with any solution :(

Let's suppose I have the following table:

[image: http://img203.imageshack.us/img203/5...board18tf.jpg]

I need the top-5 people with the top 5 highest scores to be transferred
into the second table automatically. Name goes to the left column, score
goes to the right one.

Ok, adding the scores is not a problem, I'm using the following
formulas:

B15 = LARGE(B2:B10;1)
B16 = LARGE(B2:B10;2)
B17 = LARGE(B2:B10;3)
B18 = LARGE(B2:B10;4)
B19 = LARGE(B2:B10;5)

Now how do I make a name going to the appropriate cell?
I've tryed using the following function
=OFFSET(A1;MATCH(LARGE(B2:B10;k);B2:B10;0);0;1;1)
where -k- is the required k-th largest number...and it works fine! As
long as there are no repeating scores.

Now look what I get when I try to do this:

[image: http://img216.imageshack.us/img216/5...board25af.jpg]

Notice second and third positions. The names are the same, while one of
them Joes is supposed to be Nick.
I guess the problem here is in MATCH function which returns the
position of the first matching value, which is then used in OFFSET
function to get a name.
So...how can I fix this?


--
Landanan
------------------------------------------------------------------------
Landanan's Profile: http://www.excelforum.com/member.php...o&userid=31262
View this thread: http://www.excelforum.com/showthread...hreadid=509352

Landanan,

Use a vlookup. First, you will have to put in a hidden column to the
right of the Score column (i.e. in column C). Make the cells = to the
Name Column (i.e. Column A). In your Top 5 Name Table in Cell A15,
type =vlookup(B15,B:C,2,FALSE). Drag the formula down for the other
four cells. Here is what the formula is doing:

1. B15 is the value you want to find.
2. B:C is the address of the table where the data is found (the
information you are looking (i.e. the value of B15) must be in the
leftmost column of the referenced table, hence the hidden column and
using the table in B:C, instead of using the table in A:B. Obviously,
you could also move the Score data from Column B to column A then
reference the table in A:B, rather than creating Column C and hiding
it.
3. 2 is the column within the table that contains the data you want
returned (i.e. the Name associated to the Score); the number is
relative to the leftmost column (i.e. since we start with Column B and
want Column C data returned, we want the 2nd column's data, but if we
started with Column A and want Column C data returned, we would want
the 3rd column's data.)
4. FALSE is optional, BUT it prevents the formula from returning a
partial match. For a partial match you can skip this part of the
formula, or you can type TRUE.

I hope this helps.

Jan

JBARR

Thanks for the reply!

Did exactly like you've suggested.
Unfortunately, looks like it doesn't work as it is supposed to...

http://img56.imageshack.us/img56/7852/clipboard13tu.jpg

I guess the VLOOKUP function works the same way as MATCH - it returns
the referrence based on the first matching value, ignoring all others
:(

Take a look at the screenshot I've included. Maybe I'm doing something
wrong there?


--
Landanan
------------------------------------------------------------------------
Landanan's Profile: http://www.excelforum.com/member.php...o&userid=31262
View this thread: http://www.excelforum.com/showthread...hreadid=509352

Ok, I've found the way!
It's a bit messy, requires 2 estra columns, but it actually works!
I haven't noticed any bugs yet.

If you guys are interested, I can post the solution (only if you're
interested, because it requires some explanations...).


--
Landanan
------------------------------------------------------------------------
Landanan's Profile: http://www.excelforum.com/member.php...o&userid=31262
View this thread: http://www.excelforum.com/showthread...hreadid=509352