+ Reply to Thread
Results 1 to 12 of 12

rank lookups

  1. #1
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    rank lookups

    Hello World!! It's me, Alex I have attached a spreadsheet. I have a list of names and a list of numbers. And then I rank the numbers, returning the values of the 4 largest numbers in the list. Now, I have a column called Name, and I would like to return the names that correspond to the values. I assume I would use an index match or something like that. Let me know Danks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: rank lookups

    You dont show a fomula for the ranking, so use this, copied down...
    =LARGE($B$2:$B$6,ROW(A1))
    And then for the names...
    =INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,0),1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: rank lookups

    Here is a variation for getting the largest numbers;

    Select 4 cells F2:F5 where you want the largest numbers to be entered then enter this formula in the formula bar:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Enter with Ctrl+Shift+Enter (array formula)

    This will list in descending order the 4 largest number in column B.

    Now use the same formula supplied by FDibbins for column G and copy down.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: rank lookups

    Here's another method that accounts for ties.

    A top/bottom list may have more than N items that fall within N if there can be ties (duplicate number values).

    In the following example I have the number values sorted in descending order just to make it easier to see what items are in the top 4. In this example there are actually 6 items that fall within the top 4.

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Name
    Score
    Top N
    4
    Name
    Score
    2
    Sue
    94
    Count
    6
    Sue
    94
    3
    Tom
    84
    Tom
    84
    4
    Bob
    84
    Bob
    84
    5
    Jim
    83
    Jim
    83
    6
    Lisa
    83
    Lisa
    83
    7
    Becky
    83
    Becky
    83
    8
    Jim
    80
    9
    Gina
    77
    10
    Tim
    75

    Enter the number of the top items you want in cell E1. If you want a top 5 list then enter 5. If you want a top 3 list then enter 3. In this example we're doing a top 4 list.

    Enter this formula in E2. This will return the count of records that fall within the top N.

    =COUNTIF(B2:B10,">="&LARGE(B2:B10,E1))

    Enter this formula in H2:

    =IF(ROWS(H$2:H2)>E$2,"",LARGE(B$2:B$10,ROWS(H$2:H2)))

    Enter this array formula** in G2:

    =IF(ROWS(G$2:G2)>E$2,"",INDEX(A:A,SMALL(IF(B$2:B$10=H2,ROW(B$2:B$10)),COUNTIF(H$2:H2,H2))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Select G2:H2 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: rank lookups

    If you want/need to break ties you could use this in column C2 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: rank lookups

    If you want to include a rank column...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Name
    Score
    Rank
    ----
    Top N
    4
    ----
    Name
    Score
    2
    Sue
    94
    1
    Count
    6
    Sue
    94
    3
    Tom
    84
    2
    Tom
    84
    4
    Bob
    84
    3
    Bob
    84
    5
    Jim
    83
    4
    Jim
    83
    6
    Lisa
    83
    5
    Lisa
    83
    7
    Becky
    83
    6
    Becky
    83
    8
    Jim
    80
    7
    9
    Gina
    77
    8
    10
    Tim
    75
    9

    Enter this formula in C2 and copy down. This will return the ranks with a tie breaker.

    =RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1

    Enter this formula in F2. This will return the count of records that fall within the top N.

    =COUNTIF(B2:B10,">="&LARGE(B2:B10,E1))

    Enter one of these formulas in H2 depending on what version of Excel you're using.

    For ANY version of Excel:

    =IF(ROWS(H$2:H2)>F$2,"",INDEX(A$2:A$10,MATCH(ROWS(H$2:H2),C$2:C$10,0)))

    For Excel versions 2007 and later:

    =IFERROR(INDEX(A$2:A$10,MATCH(ROWS(H$2:H2),C$2:C$10,0)),"")

    Enter this formula in I2:

    =IF(H2="","",VLOOKUP(H2,A$2:B$10,2,0))

    Select H2:I2 and copy down until you get blanks.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: rank lookups

    Here is your original workbook with formulae to rank the values and to break ties if they exist then list the 4 largest values with the name associated with them.
    Attached Files Attached Files

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: rank lookups

    If you break ties and only include the 4 largest (based on the sample data in my post) then some people are getting screwed!

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: rank lookups

    There were no parameters given for how open-ended the problem is. No matter what the formula is, someone can get "screwed". As long as the tie breaking rules are known and understood beforehand there shouldn't be problems. I have seen a coin-toss as the tie breaker in a contest after all the rules for tie-breaking had been exhausted.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: rank lookups

    That may be true but statistically speaking, the technique I demonstrated is the correct method for generating a top/bottom list unless special considerations are to be made.
    Last edited by Tony Valko; 09-07-2013 at 03:36 PM.

  11. #11
    Registered User
    Join Date
    06-29-2016
    Location
    us
    MS-Off Ver
    2010
    Posts
    2

    Re: rank lookups

    I used the formula that entered in H2, enable me to extract the 5 top percentages, however, it doesn't enable me to match the names with the percentages.

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: rank lookups

    Please start your own thread. Adding your problem on to a thread that is 3 years old is hijacking.

    Just start a new thread and include a workbook that demonstrates your problem.
    Directions:
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  2. Formula to Rank and populate values base don Rank
    By Excel Dumbo in forum Excel General
    Replies: 3
    Last Post: 10-09-2012, 02:02 AM
  3. Replies: 1
    Last Post: 08-15-2005, 05:05 PM
  4. Replies: 2
    Last Post: 05-16-2005, 12:07 AM
  5. Replies: 1
    Last Post: 05-15-2005, 07:07 PM

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