+ Reply to Thread
Results 1 to 4 of 4

How to: Return next value w/ same # in index match large function?

  1. #1
    Registered User
    Join Date
    01-24-2011
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003
    Posts
    2

    How to: Return next value w/ same # in index match large function?

    Hey, I'm new here and on a short timeline so please excuse the lack of an intro post. I'll post one later because I plan to come back for more help in the future.

    Any how I'm working on a list like this:

    Company 1 14
    Company 2 5
    Company 3 10
    Company 4 6
    Company 5 10

    I'm trying to get it to list the companies in order from greatest to least. How ever there are two companies with the same value, so when it gives me the data it goes:

    Company 1
    Company 3
    Company 3
    Company 4
    Company 5

    Here is the code I'm using:

    =INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0),1)
    =INDEX(A1:A5,MATCH(LARGE(B1:B5,2),B1:B5,0),1)
    =INDEX(A1:A5,MATCH(LARGE(B1:B5,3),B1:B5,0),1)
    =INDEX(A1:A5,MATCH(LARGE(B1:B5,4),B1:B5,0),1)
    =INDEX(A1:A5,MATCH(LARGE(B1:B5,5),B1:B5,0),1)

    Respectively.

    Is there a way to get excel to exclude the previous result or return the next value?

    Thank you very much

    -Yoshi
    Last edited by Yoshi; 01-24-2011 at 09:51 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to: Return next value w/ same # in index match large function?

    Add formula to get highest to lowest numbers first,

    Say in E1 enter:
    Please Login or Register  to view this content.
    copied down

    Then in F1:
    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER and copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-24-2011
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to: Return next value w/ same # in index match large function?

    You are my hero.

  4. #4
    Registered User
    Join Date
    03-05-2015
    Location
    Israel
    MS-Off Ver
    Office 2007
    Posts
    1

    Re: How to: Return next value w/ same # in index match large function?

    Works like a charm but could some one explain the logic of the formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1