INDEX formula in Excel Top 10 repeats previous value

0 votes

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.

enter image description here

The values are taken over from this table:

enter image description here

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 by Kithuzzz
• 38,000 points
1,036 views

1 answer to this question.

0 votes

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

   )
answered Oct 8, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
1,790 views
0 votes
1 answer
0 votes
0 answers

Formula in Excel with a particular increment value

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

Apr 10, 2023 in Others by Kithuzzz
• 38,000 points
378 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
1,290 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
710 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,600 points
952 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
1,513 views
0 votes
1 answer

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

answered Dec 17, 2022 in Others by narikkadan
• 63,600 points
1,627 views
0 votes
1 answer
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,600 points
813 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP