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

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.

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.