+ Reply to Thread
Results 1 to 6 of 6

Whick lookup needed to return a column heading?

  1. #1
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Whick lookup needed to return a column heading?

    I am looking for a value in a table array then need to return the column header of the column in which it is found. See attached simple spreadsheet. In Cell B2, the value in cell E1 should be returned. Slight complication is that it shouldn't be an exact match - if it only contains the lookup value, it should still return it. Lookup example.xlsx

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

    Re: Whick lookup needed to return a column heading?

    Well for simple examples like that this works
    =SUMPRODUCT(($D$2:$K$25=LEFT(A2,2))*COLUMN($A$1:$H1))
    "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

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Whick lookup needed to return a column heading?

    Using your posted workbook....
    this regular formula, copied down, looks for the left letters in Col_A in the table and assigns a Zone to each postcode:
    Please Login or Register  to view this content.
    So if Col_A contained L52, the formula returns: 2
    If the Col_A value is blank or not a match...the formula returns: 0

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Re: Whick lookup needed to return a column heading?

    Thank you Martin, excellent. Looks like both work, I have used Martins as it is shorter - thank you Martin and Ron.

  5. #5
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Re: Whick lookup needed to return a column heading?

    Quote Originally Posted by martindwilson View Post
    Well for simple examples like that this works
    =SUMPRODUCT(($D$2:$K$25=LEFT(A2,2))*COLUMN($A$1:$H1))
    Martin - I have discovered a small problem when the postcode is only a single digit, not two - your formula looks for 2 - (A2,2) - can this be amended so it accepts both one and two digit postcodes?

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

    Re: Whick lookup needed to return a column heading?

    thats why ron suggested a longer version!

+ 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. Replies: 6
    Last Post: 02-25-2014, 10:49 AM
  2. lookup values in a range,return corresponding column heading
    By aljaffa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2011, 04:31 PM
  3. Whick lookup formula to use
    By usticm in forum Excel General
    Replies: 1
    Last Post: 02-08-2011, 10:25 AM
  4. Replies: 7
    Last Post: 04-16-2009, 01:03 PM
  5. Lookup min & column heading
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 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