+ Reply to Thread
Results 1 to 7 of 7

Return the Row Heading with Max Value in Table

  1. #1
    Registered User
    Join Date
    03-16-2014
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2007
    Posts
    52

    Return the Row Heading with Max Value in Table

    I uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have sepereate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.

    Essentially from my months listed is seperate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.

    I've been trying some Index and Match formulas, but can't seem to figure it out to return right. Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Return the Row Heading with Max Value in Table

    In B9

    =INDEX($A$3:$A$6,MATCH(MAX(INDEX($B$3:$D$6,,MATCH(A9,$B$1:$D$1,0))),INDEX($B$3:$D$6,,MATCH(A9,$B$1:$D$1,0)),0))

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    03-16-2014
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Return the Row Heading with Max Value in Table

    Thanks so much, this is going to help me out so much!

  4. #4
    Registered User
    Join Date
    03-16-2014
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Return the Row Heading with Max Value in Table

    Do you think we could take this one step further and say Mar had no data yet, could we manipulate it to return " ". Otherwise it pulls the first row heading from the listing
    Last edited by BLS99covert; 03-23-2014 at 04:11 PM.

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,371

    Re: Return the Row Heading with Max Value in Table

    Add it with IFERROR:

    =IFERROR(INDEX($A$3:$A$6,MATCH(MAX(INDEX($B$3:$D$6,,MATCH(A9,$B$1:$D$1,0))),INDEX($B$3:$D$6,,MATCH(A9,$B$1:$D$1,0)),0)),"")

    Thanks

  6. #6
    Registered User
    Join Date
    03-16-2014
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Return the Row Heading with Max Value in Table

    Unfortunately, that didn't quite solve my issue. It is still pulling the first row heading, because techinally the formula isn't in error. There are just zeros right now.

  7. #7
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Return the Row Heading with Max Value in Table

    If the cells in the Mar column are truly blank the IFERROR formula provided by azumi should work.

    If they are 0, then try

    Please Login or Register  to view this content.

+ 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. Replies: 6
    Last Post: 12-11-2013, 05:57 AM
  2. [SOLVED] Return an array which changes according to latest date in heading at the top of table
    By David Brown in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-21-2013, 07:16 AM
  3. Replies: 7
    Last Post: 01-25-2013, 05:17 PM
  4. Return dates from a column heading in a table of arrows
    By Beeblebrox in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2012, 09:12 PM
  5. [Macro Help] Search Column in a Table and Return Heading
    By x3ldonx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2012, 06:18 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