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 in Others by Kithuzzz
• 20,660 points
62 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 by narikkadan
• 37,660 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 in Others by narikkadan
• 37,660 points
43 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 in Others by narikkadan
• 37,660 points
89 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 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

Moving Average formula in Excel not autofilling in table

You could use AVERAGEIFS to make the ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
70 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 in Others by narikkadan
• 37,660 points
58 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 in Others by narikkadan
• 37,660 points
43 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 in Others by narikkadan
• 37,660 points
35 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
210 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 in Others by narikkadan
• 37,660 points
39 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