+ Reply to Thread
Results 1 to 14 of 14

Return column title if value present

  1. #1
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Return column title if value present

    Right, apologies, I have let my excel skills suffer through lack of use. This hsould be easy.

    ROW 1 - list of headings
    ROW 2 - blank cells that may contain an "X"

    At the end of Row 2 I want it to find out which is the last column with an "X" in it, i.e. F2, and return the title of that column, i.e. F1.

    Any ideas?
    Last edited by ChrisMattock; 08-14-2009 at 11:49 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    You don't specify columns in play but let's assume A:Z

    =INDEX($A$1:$Z$1,MATCH(REPT("Z",255),$A2:$Z2))

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Return column title if value present

    =index(b1:m1,1,match("*",b2:m2,-1))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    Well that's fantastic, now I have another question.

    If the value in Row 1 contains "AAA" and also does not contain "BBB", then I need to look for the previous column that contained a corresponding "X". Possible?

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    Not quite sure I follow the AAA/BBB requirement... if we say it should contain AAA then I would probably opt for something like:

    =LOOKUP(2,1/(ISNUMBER(SEARCH("AAA",$A$1:$Z$1))*ISERR(SEARCH("BBB",$A$1:$Z$1))*($A2:$Z2="X")),$A$1:$Z$1)

  6. #6
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    That's brilliant but not quite there...

    Basically if the cell contains:

    "AAA" and "BBB" then it's valid
    "AAA" then it's valid
    "BBB" then it is not valid and the previous corresponding cell marked with "X" should be taken.

    Does that make sense?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    [deleted - misread]

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    In hindsight my prior post might have been right after all... given AAA is seemingly a pre-requisite why not just test for that and ignore BBB altogether, ie:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    Actually that isn't quite true, "AAA" and "BBB" may both not appear in the 1st row the logic is:

    If BBB is present without AAA then the column is not valid.
    All other combinations are valid.

    Really sorry - I am sure I used to be able to do this on my own, but I can't even get my head around this formula right now!

  10. #10
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    This is the kind of logic, I can get it to return a TRUE/FALSE using a single cell, however not incorporating it into the lookup formula.

    Please Login or Register  to view this content.

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    My point is/was:

    AAA = Valid
    AAABBB = Valid
    BBB = Not Valid

    The common truth is that AAA must appear in the string, BBB is irrelevant.... or are you saying there are a plethora of other values other than AAA that are valid, eg CCC, DDD, DDE, DDEAAA etc... but BBB is only valid if with AAA, ie BBBCCC not valid etc.. ?
    Last edited by DonkeyOte; 08-14-2009 at 11:34 AM.

  12. #12
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    Yes indeed, a vertiable plethora (good word ) of alternatives.

    AAA may very well not appear in the string, the common truth is that BBB is only valid if AAA is there, BUT everything else is always valid.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Return column title if value present

    OK, slightly annoyed at myself because I did this yesterday and then opted against posting it... perhaps:

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Return column title if value present

    My man - you have come up trumps, thanks so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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