+ Reply to Thread
Results 1 to 4 of 4

Formula to return headers for 3 highest values in a row into the same cell

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Formula to return headers for 3 highest values in a row into the same cell

    I have been at this for a little while now because I really wanted to figure it out on my own but after using match, max, and large the closest I got is the highest value and then another value duplicated which I don't understand how it picked it. I am trying to do a formula that will list the 3 highest values in column U for each set (row) listed by the header in row 34. Cell U36 is a proper example.

    The example is a copy of the sheet where the info is. All the dollar values are actually formulas in the original sheet if that makes a difference.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Formula to return headers for 3 highest values in a row into the same cell

    Use this in U35:

    =INDEX($L$34:$T$34,MATCH(MAX(L35:T35),L35:T35,0))&","&INDEX($L$34:$T$34,MATCH(LARGE(L35:T35,2),L35:T35,0))&","&INDEX($L$34:$T$34,MATCH(LARGE(L35:T35,3),L35:T35,0))

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,131

    Re: Formula to return headers for 3 highest values in a row into the same cell

    i may not be following correctly
    to return the headers matching the 3 highest values
    I have
    =INDEX($L$34:$T$34, MATCH(LARGE($L35:$T35, 1), $L35:$T35, 0))&","&INDEX($L$34:$T$34, MATCH(LARGE($L35:$T35, 2), $L35:$T35, 0))&","&INDEX($L$34:$T$34, MATCH(LARGE($L35:$T35, 3), $L35:$T35, 0))

    CI,ST,SP
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Re: Formula to return headers for 3 highest values in a row into the same cell

    Thank you both of you. Another example of more than one way to solve the problem with excel(that I kept missing lol). There is 1 other thing though. In my example workbook, Q35 and T35 have the same value. If I change R35 to 0, then Q35 and T35 would be tied for 2nd highest and O35,Q35, and T35 would be the 3 highest values but both formulas return the headers of CI,WG,WG (O35,Q35,Q35) instead of the 3 highest. Can either formula be corrected to account for this?

+ 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. [SOLVED] Formula to return header for 2 highest values in a row, into the same cell
    By RICK JAMES in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2021, 10:50 PM
  2. Replies: 2
    Last Post: 02-24-2017, 07:35 PM
  3. Replies: 4
    Last Post: 01-19-2017, 03:48 PM
  4. [SOLVED] Max formula to return total of highest, second highest and third highest value
    By JonWilf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2016, 08:20 AM
  5. [SOLVED] Formula to Lookup the highest Value in Col and return Values Cols in same Row
    By hammer2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2015, 04:17 AM
  6. Finding the highest of 3 values, but only headers appear in text string
    By DaveBre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2014, 06:14 PM
  7. Replies: 2
    Last Post: 05-09-2012, 01:30 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