+ Reply to Thread
Results 1 to 4 of 4

Return column header based on column criteria and number value

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Return column header based on column criteria and number value

    Hello-

    I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26). Any help greatly appreciated. Here's data table:

    Criteria Header 1 Header 2 Header 3 Header 4
    ZZZ 5.00 15.00 50.00 130.00
    ZZ 5.00 15.00 50.00 130.00
    Z 5.00 15.00 50.00 130.00
    YYY 5.00 15.00 50.00 130.00
    YY 5.00 15.00 50.00 130.00
    Y 5.00 15.00 50.00 130.00
    DDD 5.00 15.00 50.00 130.00
    DD 5.00 15.00 25.00 75.00
    D 5.00 15.00 20.00 65.00
    RRR 5.00 15.00 15.00 45.00
    RR 2.50 10.00 10.00 30.00
    R 1.50 3.00 10.00 25.00
    UUU 0.75 1.50 5.00 20.00
    UU 0.75 1.50 5.00 20.00
    U 0.75 1.50 5.00 20.00
    P 0.75 1.50 5.00 20.00
    T 0.10 0.10 5.00 18.00

    Criteria Number Value
    DDD 10.00 Header 1 >>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Return column header based on column criteria and number value

    Here you go.

    I combined INDEX, MATCH, and OFFSET.

    batman.xlsx

    It an array formula, confirmed with Ctrl+Shift+Enter.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    02-07-2014
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Thumbs up Re: Return column header based on column criteria and number value

    Awesome! I've been struggling with this for a while. Many thanks, love the headers!

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Return column header based on column criteria and number value

    Yeah, I had too much sugar today. Glad it worked!

    Go teamwork!

    | (• ◡•)|/ \(❍ᴥ❍ʋ).

+ 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. [SOLVED] Return Value from column based on header value
    By SAsplin in forum Excel General
    Replies: 5
    Last Post: 07-16-2013, 07:46 AM
  2. [SOLVED] Formula to find first negative number in row and return column header
    By spoonedmango in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2012, 02:08 PM
  3. Replies: 6
    Last Post: 07-26-2012, 06:34 PM
  4. [SOLVED] Trying to return a column header in a cell based on a value
    By brianfromla in forum Excel General
    Replies: 2
    Last Post: 06-26-2012, 10:37 AM
  5. Have a column return a phrase based on a number in another column
    By luke.guthrie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2012, 04:04 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