Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting Captions (Excel 2003)

    I have column B looking up a Maximum number from col's D-K. I want to have column C showing the column caption from D-K associated with the maximum amount. I am not sure how to do this. The MAX function returns the value, not the address. I am attempting to find a way to use the OFFSET function to refernce that row 1 column caption. Is this the correct approach? Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting Captions (Excel 2003)

    Presuming B2 has:
    <pre>=MAX(D2:K2)</pre>


    Then in C2 you could have:
    <pre>=INDEX($D$1:$K$1,MATCH(B2,D2:K2,0))</pre>


    Then you could copy this down the rows and it would indicate for each row what the column Heading is for the max value.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Captions (Excel 2003)

    Works like a charm! Would not have thought of using Index Match like that. Thanks.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting Captions (Excel 2003)

    The INDEX/MATCH combo It is a workaround to do a HLOOKUP with a negative row or VLOOKUP with a negative column (see eg <post:=244,408>post 244,408</post:>)

    Steve

Posting Permissions

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