Forum Discussion

Lukmanayahayya's avatar
Lukmanayahayya
Copper Contributor
Dec 08, 2022

Displaying 1st, 2nd, 3rd.......nth.

Hi there. 

I use this rank formula to get the individual to get rank the performance of students as 1st, 2nd,3rd etc. which I will later match using index match function. 

 

The issue is that the rank function only displays 1, 2, 3, etc. but I want it to display the results as 1st, 2nd,3rd, 4th etc.

I hope someone can help on this.

Thanks

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 08, 2022

    Lukmanayahayya 

    This formula would cover you from positions 1-20. I kept your original formula and added the ordinal.

    =LET(position,INDEX($A$2:$M$7,MATCH($H$12,$A$2:$A$7,0),MATCH(D24,$A$2:$M$2,0)),position&VLOOKUP(position,{1,"st";2,"nd";3,"rd";4,"th"},2))

Resources