+ Reply to Thread
Results 1 to 9 of 9

need to find a column name from known row name and cell value

  1. #1
    Registered User
    Join Date
    04-13-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2010
    Posts
    6

    need to find a column name from known row name and cell value

    Hi all,

    I have tried everything and cannot seem to figure this out.

    I have 2 known (but constantly changing) values. One is a row label and the other is a value that will be in that specific row of the table. I need to make an excel formula that will look for that row, then go within that row and find the value then return the the column header.

    Here is an example table:

    brown gold silver
    dog 1 2 3
    cat 3 2 1
    fly 2 3


    So I want to find the row 'cat' then return the name of the header that belongs to the value 2 (ie i want my result to be gold)

    Here is the formula that I currently have.. I feel like im almost there but not quite:

    where in A1 I have the number 2
    and B1 has cat
    the entire table is in b2:k78 with c2:k2 being the headers.. (brown, gold, silver..etc) and b3:b78 being the types of animals (dog, cat, fly etc)

    =LOOKUP(a1,INDEX('ranking'!$C$3:$K$78,MATCH(b1,'ranking'!$B$3:$B$78,0),0),'ranking'!C2:K2)

    This will not work!! Please help me figure this out

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: need to find a column name from known row name and cell value

    Try this array formula:

    =INDEX($C$2:$E$2,SUMPRODUCT(($B$3:$B$5=B1)*(C3:E5=A1)*(COLUMN($C$3:$E$5)-2)))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    Check it out in the sheet fiorst, as I'm not 100% certain that I have your data table in the right place..
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,359

    Re: need to find a column name from known row name and cell value

    Hi mmarchi and welcome to the forum,

    Try something like this.
    =INDEX(1:1,MATCH(C8,INDIRECT("A"&MATCH(C7,A:A,0)&":D"&MATCH(C7,A:A,0)),0))

    see the attached.
    Column head for a block array.xlsx

    Array formula not needed, like Glenn's formula.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: need to find a column name from known row name and cell value

    Mmm. But two whole column matches with an INDIRECT....

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,359

    Re: need to find a column name from known row name and cell value


  6. #6
    Registered User
    Join Date
    04-13-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2010
    Posts
    6

    Re: need to find a column name from known row name and cell value

    PERFECT! This worked wonderfully. Thanks so much for your help Glenn! .. MarvinP i tried yours as well but it didnt work. I was getting volatile warning signs in the equation helper thing so perhaps your equation couldnt handle my data?
    Last edited by mmarchi; 04-13-2017 at 03:47 PM. Reason: add some more detail

  7. #7
    Registered User
    Join Date
    04-13-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2010
    Posts
    6

    Re: need to find a column name from known row name and cell value

    Glenn one more thing.. what if the value im looking for is blank.. so if im searching for the 2 but there's actually no 2 .. i need it show an error or a blank. It doesn't appear to be doing that..its pulling the header associated with number 1.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: need to find a column name from known row name and cell value

    OK. Once again, an array formula:

    =IF(OR($A$1="",$B$1=""),"Error",INDEX($C$2:$E$2,SUMPRODUCT(($B$3:$B$5=B1)*(C3:E5=A1)*(COLUMN($C$3:$E$5)-2))))

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: need to find a column name from known row name and cell value

    Hi All (a late answer),

    a very similar approach to mmarchi's one

    =LOOKUP(a1,INDEX('ranking'!$C$3:$K$78,MATCH(b1,'ranking'!$B$3:$B$78,0),0),'ranking'!C2:K2)[/QUOTE]

    could become

    =IFERROR(INDEX('ranking'!C2:K2,MATCH(A1,INDEX('ranking'!$C$3:$K$78,MATCH(B1,'ranking'!$B$3:$B$78,0),),0)),"")

    Regards
    Last edited by canapone; 04-14-2017 at 03:37 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

+ 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. Find 1st unique Value on Column and Insert certain Word on cell on left Column
    By Wldlife23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2013, 10:02 AM
  2. [SOLVED] Macro to find the empty cell in a column and copy a adjacent row to another column.
    By naga in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-18-2013, 07:44 AM
  3. Replies: 4
    Last Post: 05-26-2013, 05:54 PM
  4. macro to find first blank cell in a column cut the value from left adjacent column
    By willykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2012, 09:23 AM
  5. Range.Find to find column and place value in next available cell in one line
    By davegugg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2011, 10:41 AM
  6. Loop to find cell, paste to new column. Turn 1 column into many.
    By d0tc0m in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2011, 09:51 AM
  7. Replies: 8
    Last Post: 08-06-2009, 09:02 AM

Tags for this Thread

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