How do I return multiple values from a single number in a list?
 

I am trying to set up table to return a value and a text statement using
VLOOKUP, but I do not know how to break a "tie" and show 2 different text
statements. Here is what I'm getting:

My data list:

Score Name
4 Johnny
6 Bob
7 Scott
6 Lewis

I want to make a table that ranks by highest value and displays the
corresponding name in the next column. I am using LARGE to sort the Score
and VLOOKUP to show the name. Here is what I get:

Rank Score Name
1 7 Scott
2 6 Bob
3 6 Bob
4 4 Johnny

How do I display all names? VLOOKUP just returns the first Name closest to
the top of the list that it sees. I would the table to show this:

Rank Score Name
1 7 Scott
2 6 Bob
3 6 Lewis
4 4 Johnny

How do I return multiple values from a single number in a list?
 

With this data in the range A2:B5 -

4...Johnny
6...Bob
7...Scott
6...Lewis

And this data in the range D2:E5 -

1...7
2...6
3...6
4...4

Enter this array formula** in F2 and copy down to F5:

=INDEX(B$2:B$5,MATCH(LARGE(A$2:A$5-ROW(A$2:A$5)/10^10,ROWS(F$2:F2)),A$2:A$5-ROW(A$2:A$5)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"flavjunior" wrote in message
...
I am trying to set up table to return a value and a text statement using
VLOOKUP, but I do not know how to break a "tie" and show 2 different text
statements. Here is what I'm getting:

My data list:

Score Name
4 Johnny
6 Bob
7 Scott
6 Lewis

I want to make a table that ranks by highest value and displays the
corresponding name in the next column. I am using LARGE to sort the Score
and VLOOKUP to show the name. Here is what I get:

Rank Score Name
1 7 Scott
2 6 Bob
3 6 Bob
4 4 Johnny

How do I display all names? VLOOKUP just returns the first Name closest
to
the top of the list that it sees. I would the table to show this:

Rank Score Name
1 7 Scott
2 6 Bob
3 6 Lewis
4 4 Johnny