Help with array formula

[Guest]

Hello friends,

I believe that the solution to my problem lies in the
writing of an array formula; but since my creativity with
writing array formula is very limited I am posing the
question to you.

Here is the scenario:
I have a spreadsheet with details on the rankings of
students in a class. We have rankings every few months
(thus several times in a year).

Thus, column A is the name of the student. Column B is
the date when the ranking was taken. Column C is the rank
of the student on that given date. Since several such
rankings are done in a year; we have in column A
duplicates.

What I want to do is to find out the ranking of each
student during the last such ranking exercise.

On a separate TAB, in column A, I have already the names
of the students (thus unique values from column A). But
now my requirement is to have on this tab a column B that
gives the ranking of the student during the
chronologically last exercise done for ranking.

Can you tell me what formula to use.

As I mentioned above, I feel that it is an array formula;
taking all the rankings for the matching student; and
finally I need to take a MAX on the date of the ranking.
But obviously I am unable to think out the algortihm on my
own.

(BTW: if array formula is not the solution then please
feel free to recommend alternative strategies).

Lots of thanks in advance for your help.

 

[Jason Morin]

If your ranking data is on a sheet called "rank", try
this in B1 of the new sheet:

=INDEX(rank!$C$1:$C$10,MATCH(A1&MAX(IF(rank!
$A$1:$A$10=A1,rank!$B$1:$B$10)),rank!$A$1:$A$10&rank!
$B$1:$B$10,0))

Array-entered (press ctrl/shift/enter). After array-
entering, fill the formula down.

HTH
Jason
Atlanta, GA

 

[Guest]

Jason,

Great formula. Works like magic.

Many thanks.