+ Reply to Thread
Results 1 to 9 of 9

Can't automatically retrieve column header in formula

  1. #1
    Registered User
    Join Date
    12-17-2015
    Location
    Portland, OR
    MS-Off Ver
    Office 2010
    Posts
    26

    Can't automatically retrieve column header in formula

    Can someone help with telling me what the formula would be to look for the value in A4 in the table F1:I25. Then when an exact match is found then return the column header of that column to C4.
    Book1.xlsx
    Last edited by Troy73; 01-05-2018 at 09:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,050

    Re: INDEX and MATCH

    Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

    • Use concise, accurate thread titles.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • Responding to a request to change your thread title by doing so is mandatory.

    To change a title go to your first post, click EDIT then Go Advanced and change your title.

    No help to be offered, please, until the OP complies with this request.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,061

    Re: INDEX and MATCH

    Try:

    =IFERROR(INDEX($F$1:$I$1,AGGREGATE(15,6,COLUMN($F$2:$I$25)/($F$2:$I$25=A5),1)-5),"")
    Last edited by Phuocam; 01-05-2018 at 09:31 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,050

    Re: Can't automatically retrieve column header in formula

    Thanks for updating the thread title. It's fine now.

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Can't automatically retrieve column header in formula

    =index($f$1:$i$1,aggregate(15,6,column($f$2:$i$25)/($f$2:$i$25=$a2),1)-5)

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Can't automatically retrieve column header in formula

    Use this:

    =IFERROR(INDEX($F$1:$I$1,SUMPRODUCT(($F$2:$I$25=A2)*(COLUMN($F$2:$I$25)-COLUMN($F$2)+1))),"")
    Attached Files Attached Files
    Glenn



  7. #7
    Registered User
    Join Date
    12-17-2015
    Location
    Portland, OR
    MS-Off Ver
    Office 2010
    Posts
    26

    Re: Can't automatically retrieve column header in formula

    I'm sorry guys, but I didn't include the entire spreadsheet just a concept of what I wanted done then I figured that I could manipulate the columns and rows for each column that is labels 'Move To' but I cannot figure it out. Really sorry for the bother but please look at the suggested formula that I put in D6. Every time that "WRONG LINE" is under the column 'Correct Line?' then under the 'Move To' columns I need this formula to return the column header where it is actually classified under P4:S41. I hope I'm clear enough.

    Book1.xlsx

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,222

    Re: Can't automatically retrieve column header in formula

    Try

    in D6

    =IFERROR(INDEX($P$4:$S$4,SUMPRODUCT(($P$5:$S$41=B6)*(COLUMN($P$5:$S$41)-COLUMN($P$5)+1))),"")


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Can't automatically retrieve column header in formula

    A non-array (ordinary) formula:

    =IF(C5="Wrong Line",IFERROR(INDEX($P$4:$S$4,SUMPRODUCT(($P$5:$S$28=B5)*(COLUMN($P$5:$S$28)-COLUMN($P$5)+1))),"No Match"),"")
    Attached Files Attached Files

+ 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] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 AM

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