+ Reply to Thread
Results 1 to 8 of 8

Return Column Header for Cell Range that equals "1"

  1. #1
    Registered User
    Join Date
    12-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Return Column Header for Cell Range that equals "1"

    Good Morning All,

    I've tried numerous Index, VLOOKUP, MATCH, HLOOKUP functions to try and make this work. UNfortunately, I can't.

    If there is a macro, that'd be awesome.

    The problem I'm having is that I'd like to have a column header be returned for rows of data by looking at a particular cell range and only return the column header that contains the value "1".

    I've attached a file to show you my dilemma.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Return Column Header for Cell Range that equals "1"

    Hi,

    Not sure why you're using text rather than numeric entries, but anyway, in G18 and copy down:

    =INDEX($G$1:$K$1,MATCH("1",INDEX($G$2:$K$11,MATCH(B18&E18&F18,INDEX($B$2:$B$11&$E$2:$E$11&$F$2:$F$11,,),0),),0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    12-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Return Column Header for Cell Range that equals "1"

    Thank you, XOR LX
    I tried that input but it unfortunately didn't work. It showed up as N/A.
    I can certainly change the numbers-format from Text to Number.
    If I do that, would it help shorten the formula?

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Return Column Header for Cell Range that equals "1"

    I used precisely the workbook that you posted and works fine for me.

    See attached.

    Regards
    Attached Files Attached Files

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Return Column Header for Cell Range that equals "1"

    P.S. You don't mention how you will resolve the issue in cases of more than one "1" per person. Tim Rick, for example, has 1s in columns for M4 and M7.

    Regards

  6. #6
    Registered User
    Join Date
    12-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Return Column Header for Cell Range that equals "1"

    Thank you, XOR LX!
    It worked for me, finally.
    I just copied/pasted the attributes first before I did the formula.
    Good question about the multiple cell values. I am uncertain as to what to do with it as of yet.
    I'd like 2 rows with each Error on their individual rows.
    But thank you. I really appreciate your help!

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Return Column Header for Cell Range that equals "1"

    You're welcome.

  8. #8
    Registered User
    Join Date
    12-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Return Column Header for Cell Range that equals "1"

    Good morning again, all.
    XOR LX, thank you for the recommendation.
    However, it gets a little cumbersome doing the formula each time since the rows continue to change.
    I'd realllllly prefer putting the error-table on its own tab and a macro to lookup/match.
    Is there any way that can be done?

+ 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: 3
    Last Post: 04-14-2013, 11:53 PM
  2. if cell equals "value" then return that entire row to another worksheet
    By juliehight in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2012, 03:36 PM
  3. VLOOKUP? If cell has a "1", then return column header
    By mattbr80 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-31-2012, 12:46 AM
  4. [SOLVED] Delete row if cell in Column N equal "Different" or Column P equals "Blank"
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2012, 08:25 AM
  5. VBA - Input Value If Column Number Equals "x" within Non-Contiguous Named Range
    By greenexcel12345 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2011, 02:23 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