INDEX formula in Excel Top 10 repeats previous value

Question

I'm searching a data table for the top 10 values. I'm using the following formula:

=LARGE($R$2:$R$26,U13)

This formula is in column V.

The values are taken over from this table:

I'm attempting to determine which country has one of those top 10 values in column W. I'm using the following formula:

=INDEX($I$2:$I$26,MATCH(V13,$R$2:$R$26,0))

Here's what's wrong: My INDEX formula repeats the first nation identified in both rows if the top ten value for two countries is the same (like in cases 9 and 10 at the bottom of my table, when two countries have the value 39).

For instance, Nigeria should be at Rank 10 since it likewise has a value of 39.

I believe I'm doing something incorrectly.

Answer 1

Try this formula in cell W4:

=IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0)))

The calculation determines whether the prior nation received the same score as the current one. In this situation, it looks for the previous nation in the list and changes the search parameters for the score. In this manner, even a perfect tie across the board will result in a list of distinct nations.

Your formula failed to resolve your issue because the INDEX function looks for the first instance of the sought value. If you have already run another INDEX in another cell, it is not taken into account. You will simply eliminate the first occurrence of the value you are searching for (as well as some other values you don't care about) by changing the range to be searched according to the prior result.

Exploding the formula we obtain this:

=IF(V3=V4,                                    'Checks for a tie
    '---------------------------------------------If it's a tie
    INDEX(                                       'Use a index function to pick the result
          INDIRECT(                                 'Use an indirect function to define the range to be searched
                   "I"&                                'State the column of the range to be searched
                   MATCH(W3,I:I,0)+1&                  'Use a match function to find the previous occurence of the score whithin the column of the range to be searched and add 1 to it to cut out that value (and any previous one)
                   ":I26"                              'State the closing cell of the range to be searched
                  ),
          MATCH(                                    'Use a match function to determine in what row of the defined range the score is occuring
                V4,                                    'The value to be searched
                INDIRECT(                              'Use an indirect function to define the range to be searched
                         "R"&                             'State the column of the range to be searched
                         MATCH(W3,I:I,0)+1&               'Use a match function to find the previous occurence of the score whithin the column of the range to be searched and add 1 to it to cut out that value (and any previous one)
                         ":R26"                           'State the closing cell of the range to be searched
                        ),
                0                                         'Specify that you want the the exact occurence
               )
         ),
    '---------------------------------------------If it's not a tie
    INDEX(                                       'Use a index function to pick the result
          $I$2:$I$26,                               'State the range to be searched
          MATCH(                                    'Use a match function to determine in what row of the range the score is occuring
                V4,                                    'The value to be searched
                $R$2:$R$26,                            'State the range to be searched
                0                                      'Specify that you want the the exact occurence
               )
         )

   )