Choose match index in access

[dastr]

Hi all,



I have a quite complicated formula that I run in excel and I am wondering whetehr there is an alternative to do this in access:

=CHOOSE(MATCH(B1,INDEX(LARGE(INDEX($B$1:$B$65536*( $A$1:$A$65536=A1),0),COUNTIF(A:A,A1)-(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)),0),0),"1st","2nd","3rd","4th") & " best"

which gives me the belowname/ score/ peronal best) -> logic if participant has only one score - this remains his/her first best; if more than 1 scores - then the lowest gets 1st best, second lowest - 2nd best, etc....see below the output:

Anna	2	1st best
Luisa	4	2nd best
Luisa	3	1st best
Angel	1	1st best
Angel	8	4th best
Angel	7	3rd best
Angel	5	2nd best
Arlene	1	1st best
Arlene	9	2nd best Thanks!!

[June7]

See if this helps http://allenbrowne.com/ranking.html