+ Reply to Thread
Results 1 to 6 of 6

Return a value from a column based on the highest number in the adjacent column

  1. 02-14-2019, 03:10 AM #1
    StanLeakup
    StanLeakup is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Seattle
    MS-Off Ver
    2010
    Posts
    26

    Return a value from a column based on the highest number in the adjacent column

    Hi all,

    Please see below for a screenshot for reference. I have a list of names (abridged) in Column B along with their corresponding scores in Column C. I figured out the formula for finding the maximum highest corresponding value using the index match function combined with max. I am trying to figure out a formula to return the name based on the next highest score, Rick, and then Luis. The formula could be directly below the name Austin and dragged down possibly? I know that I could use the filter tool but wanted to see if there was a more dynamic way.

    Thank you!


    Top 3 names in column based on corresponding value.JPG
    Reply With QuoteRegister To Reply
  2. 02-14-2019, 03:32 AM #2
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,367

    Re: Return a value from a column based on the highest number in the adjacent column

    Hope this helps
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 02-14-2019, 04:01 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Return a value from a column based on the highest number in the adjacent column

    In G2

    =IFERROR(INDEX($A$1:$A$11,MATCH(LARGE($B$1:$B$11,ROWS($G$2:$G2)),$B$1:$B$11,0)),"")

    In H2

    =IFERROR(LARGE($B$1:$B$11,ROWS($G$2:$G2)),"")

    Then copy down both til blank cell is seen.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  4. 02-14-2019, 02:07 PM #4
    StanLeakup
    StanLeakup is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Seattle
    MS-Off Ver
    2010
    Posts
    26

    Re: Return a value from a column based on the highest number in the adjacent column

    Thanks, guys. kvsrinivasamurthy: if some of the scores are the same (not in the example I am showing), it appears only one of the names are pulled. Is there any way to adjust the formula so it grabs the other names that have the same score?
    Reply With QuoteRegister To Reply
  5. 02-14-2019, 02:09 PM #5
    StanLeakup
    StanLeakup is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Seattle
    MS-Off Ver
    2010
    Posts
    26

    Re: Return a value from a column based on the highest number in the adjacent column

    See below for reference. I changed Sonya's score to 92 now and it is the same as Luis.

    Top 3 names in column based on corresponding value v2.JPG
    Reply With QuoteRegister To Reply
  6. 02-14-2019, 02:20 PM #6
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Return a value from a column based on the highest number in the adjacent column

    option, using sample file in post#3

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return row value for highest number in column
    By KimMorgan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2017, 10:54 AM
  2. Return all values in a column based on a specific value in an adjacent column
    By miikell in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 04-17-2013, 09:51 PM
  3. [SOLVED] Find, Return 1st number/word in adjacent column
    By wkhor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2012, 12:10 AM
  4. Autofill a column based on number in adjacent column
    By fbonetti in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2011, 04:19 PM
  5. Excel 2007 : Return Max Date in column based on lookup in adjacent column
    By JGradowitz in forum Excel General
    Replies: 1
    Last Post: 01-05-2011, 05:18 PM
  6. Pick out highest number in column then return a different cell
    By Accendo in forum Excel General
    Replies: 2
    Last Post: 02-23-2008, 08:28 PM
  7. [SOLVED] choose the highest value in a row and return column number
    By jlburke4 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2005, 02:50 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1