Comparing range and return top running numbers

Hi,

I need a formula to compare a range of numbers and return which is 1st, 2nd, 3rd and so on.

Assuming I got the first one right, if there were 10 rows, what do I need to add in the formula so that the rest can return with 2nd, 3rd, 4th, ....?
=IF(MAX(A1:A10)>B1,"","1st")

I attach a sample workbook.
comparison.xlsx


Thanks in advance!

In your example, in C2 try:

=INDEX({"1st","2nd","3rd","4th","5th","6th","7th","8th"},MATCH(B2,INDEX(LARGE($B$2:$B$8,ROW(INDIRECT("1:"&COUNT($B$2:$B$8)))),0),0))

Not sure how you want it do deal with duplicate values, though.

That's a good point. If duplicated value detected, then return "Tie with x" where x is the value between 1st and xx?

Like this, maybe?

=IF(COUNTIF($B$2:$B$8,B4)>1,"Tie with ","") & INDEX({"1st","2nd","3rd","4th","5th","6th","7th","8th"},MATCH(B4,INDEX(LARGE($B$2:$B$8,ROW(INDIRECT("1:"&COUNT($B$2:$B$8)))),0),0))

Hi all,

The outcome is still a little funny but it'll do for now
Thanks to all for the brainstorm!