Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Need to return column heading of value in a row

  1. #1
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Need to return column heading of value in a row

    I have a table of days for various feelings. Ages are going down column A. In row 1, columns B through G are the feelings. For example:
    sad happy lonely
    ages from to from to from to
    11 1 10 11 20 21 30
    22 1 11 12 21 22 31
    33 1 9 10 19 20 29
    44 1 8 9 18 19 28
    55 1 12 13 23 24 33
    age 22
    day 15
    result happy
    The user will be asked for the age and the day (A10 & A 11). I need to get the column name where the number is found. The day given could be in between ranges.
    Can anyone help me get the result using INDEX AND MATCH.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    Are you open to re-arranging that table a little?
    Is the first age range 0-11 or 11-21 ?
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    I've assumed it's 11-21, and 0-10 just isn't included.

    Delete the to columns, they're not necessary.
    So you have columns
    A ages
    B sad
    C happy
    D lonely

    Try
    =LOOKUP(B11,INDEX(B2:D6,MATCH(B10,A2:A6),0),B1:D1)
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    ... Also wondering whether the numbers will always be in ascending order L to R?

    Perhaps everyone is sad at the start of the month, happy in the middle and lonely at the end?

    And why do the oldies get 33 days a month!

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    Quote Originally Posted by StephenCrump View Post

    Perhaps everyone is sad at the start of the month, happy in the middle and lonely at the end?

    And why do the oldies get 33 days a month!
    I'm gonna go out on a limb and guess that this isn't the real verbiage of the table. Not really about moods and ages.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    no, the ages are just 11, 22, 33, 44 and 55.
    you can rearrange them.

  7. #7
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    thanks. but can you also do it using INDEX and MATCH?

  8. #8
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    Quote Originally Posted by Jonmo1 View Post
    Are you open to re-arranging that table a little?
    Is the first age range 0-11 or 11-21 ?

    no, the ages are just 11, 22, 33, 44 and 55.
    you can rearrange them.

  9. #9
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    Quote Originally Posted by Jonmo1 View Post
    I've assumed it's 11-21, and 0-10 just isn't included.

    Delete the to columns, they're not necessary.
    So you have columns
    A ages
    B sad
    C happy
    D lonely

    Try
    =LOOKUP(B11,INDEX(B2:D6,MATCH(B10,A2:A6),0),B1:D1)

    thanks. but can you also do it using INDEX and MATCH?

  10. #10
    New Member jrn1392's Avatar
    Join Date
    Feb 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to return column heading of value in a row

    Quote Originally Posted by StephenCrump View Post
    ... Also wondering whether the numbers will always be in ascending order L to R?

    Perhaps everyone is sad at the start of the month, happy in the middle and lonely at the end?

    And why do the oldies get 33 days a month!

    Yes, the numbers are in ascending order L to R. It’s just a sample table.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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