Forum Discussion

itsMonty's avatar
itsMonty
Brass Contributor
Jul 07, 2025

How to correct "#N/A" when using Rank function and Index function

I am trying to create somewhat of a scoreboard that eliminates the lowest ranking player per level. The problem is, sometimes the players have the same score and my formulas end up showing the them a...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jul 08, 2025

    alternatively you could use:

    =RANK(C2,$C$2:$C$8,0)+A2/100000

    for the rank and then:

    =INDEX(B$2:B$8,MATCH(SMALL($H$2:$H$8,$A2),$H$2:$H$8,0))

    for the Leading and Points

    Then format the RANK column to not show the decimal places and then you won't confuse people why the "same score" results in different "rank"

    or in newer versions of Excel you can just use:

    =SORT(B2:.C8,2,-1)

    and it will spill out all the leaders and points in order (don't need to adjust the Rank at all)

     

Resources