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
• 28,900 points
163 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
• 53,520 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
• 53,520 points
208 views
0 votes
1 answer
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
• 53,520 points
189 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 53,520 points
99 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
• 53,520 points
129 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
• 53,520 points
141 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
• 53,520 points
147 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
• 53,520 points
113 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
• 53,520 points
132 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