+ Reply to Thread
Results 1 to 6 of 6

Match value and return multiple headers

  1. #1
    Registered User
    Join Date
    07-23-2014
    Location
    portland, or
    MS-Off Ver
    microsoft 2010
    Posts
    6

    Match value and return multiple headers

    I have a sheet with row 1 as my header: ID #s. (over 274 of them)
    I need search the rest of the rows for M and then return the ID#s.

    Currently, I am using this in column A for row 2 as an example:

    =INDEX($I$1:$JV$1, MATCH("M",INDEX(I2:JV2,0),0))

    But this only returns the first ID# value with M. In some, but not the majority, I have more than one ID# with M. Does anyone know how I can return more than one header with the matching value (such as in row3)?

    Thanks!
    Holly
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,368

    Re: Match value and return multiple headers

    Perhaps something like this? Using array formulas
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-23-2014
    Location
    portland, or
    MS-Off Ver
    microsoft 2010
    Posts
    6

    Re: Match value and return multiple headers

    That is close, but each row needs to keep it own ID#s. The IDs are specific to each mutation.

    For example: Row 2: 67909
    Row 3: 64556, 64557, 64561, 64562, etc....

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Match value and return multiple headers

    I think you need a VBA solution. If you are open to a VBA solution, please find the attached sheet to see if this works as per your requirement. Click on the Green Button to get the desired IDs in col. A.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    07-23-2014
    Location
    portland, or
    MS-Off Ver
    microsoft 2010
    Posts
    6

    Re: Match value and return multiple headers

    wow! That is awsome.

    So I can appply that to other letters and sheets by modifying: If cell1 = "M" Then

    Thanks!

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Match value and return multiple headers

    Yes you can. You only need to change "M" in IF cell1 = "M" line of code with the other letter of interest like "A".

+ 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. Search for value in multiple columns, Return Column Headers
    By Zach51215 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2013, 06:49 AM
  2. Replies: 2
    Last Post: 05-09-2012, 01:30 PM
  3. Replies: 2
    Last Post: 06-29-2011, 01:36 PM
  4. Using MATCH function with multiple headers?
    By Dowler in forum Excel General
    Replies: 7
    Last Post: 07-21-2009, 08:03 AM
  5. Index Match Multiple Colums Headers
    By smigom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2008, 04:49 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