+ Reply to Thread
Results 1 to 4 of 4

INDEX, MATCH multiple values in single row, return header value(s)

  1. #1
    Registered User
    Join Date
    07-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    INDEX, MATCH multiple values in single row, return header value(s)

    I have a RACI chart outlining responsibilities, accountabilities, etc. for various team members on multiple projects. (See attached doc.) Using this data, I would like to be able to look up a given project and return, for example, the names of the team members who have been assigned the letter C for that project.

    I've found other examples of how to do this when the value being looked up is arranged in a column rather than a row, and I've found examples that return a single header rather than multiple headers if there are multiple values, but nothing that will look up multiple instances of a value in a row and then return (potentially) multiple header values.

    Helper columns are fine, VBA is not due to how this workbook will be used by others. There may be cells that contain two different letters, in which case the formula will need to be able to return headers for both "C" and "R C" columns, for instance.

    Thanks!

    RACI_chart_test.xls.xlsx
    Last edited by grrliz; 12-11-2014 at 06:19 PM.

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

    Re: INDEX, MATCH multiple values in single row, return header value(s)

    I went with an array formula, combining INDEX, SMALL, IF, ISNUMBER, and SEARCH.

    I set B18 as target project, B19 as target letter.

    B21:
    Please Login or Register  to view this content.
    Confirm array formula with Ctrl+Shift+Enter. Copy across 10 cells.
    Attached Files Attached Files
    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
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: INDEX, MATCH multiple values in single row, return header value(s)

    Hello grrliz,

    Attached is a modified version of your sample workbook.
    In Cell B16 is a drop-down list containing the project names
    In cell B17 is a drop-down list for the R,A,C,I letters
    In cell B20 and copied down is this formula to return the employee names (column headers) that meet the selected criteria:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: INDEX, MATCH multiple values in single row, return header value(s)

    Thank you both, this works perfectly!

+ 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: 7
    Last Post: 10-10-2014, 05:40 PM
  2. Using Index/Match to return multiple values for one match
    By superboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 06:21 PM
  3. Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  4. Need to look up a value and return multiple values-INDEX/MATCH?
    By abigail99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2012, 10:05 AM
  5. [SOLVED] How to use Index Match to return multiple values
    By pingpoeng in forum Excel General
    Replies: 2
    Last Post: 04-09-2012, 09:58 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