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.

Oct 7, 2022 in Others 447 views

## 1 answer to this question.

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
)
)

)```
• 63,160 points

## How to increment row value in an index function in excel?

Try this- =INDEX(\$F\$27:\$F\$40,COLUMN(A\$1)) It will automatically increase the row ...READ MORE

## INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Step through the formula that produces the ...READ MORE

## Formula in Excel with a particular increment value

I need to use Excel formulas for ...READ MORE

## How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

## Return blank cell only if referred cell is blank, but return aging if date is entered

Try this: =IF(ISBLANK(AC2),"",TODAY()-AC2) The TODAY  function automatically refreshes based ...READ MORE