Match across multiple columns and return multiple values?

Question

I have a workbook with multiple sheets. On the first sheet is a list of projects with basic criteria, including the project team members. Each name is listed in its own column in the same row as the project name, like so:

The next sheet lists the team members' names. Under each of their names, I want to autopopulate the names of the projects they work on, based on the info contained in the previous sheet. I have tried several different formulas to accomplish this. Using the CONCATENATE formula, I was able to search across the multiple columns to pull the name of the first project- but couldn't figure out how to get the vlookup (or index match) to display multiple project names (if the team member is working on multiple projects at the same time).

Is there a formula that might work?

EDITED to include desired output (the cells under Joe/Mary/Frank would be populated by the info in the table shown above):

output

Please include a mockup of what the output should look like. — Scott Craner
If you want it in the same cell, then you need Office 365 or vba. — Scott Craner
Do you want to display them all in the same cell or if you have your team member name as your column heading, can each project be listed under that heading in the column? e.g. JOE column heading, column B. Cell B2=Project1, B3=Project2 — Carol
They don't need to display in the same cell. I have 8 blank cells beneath each team member's name, and want to fill those cells (as many as applicable) with the name of the projects. — rubitish

Carol Carol · Accepted Answer · 2017-12-19T01:24:17

This is set to work for your example range, Row 1 as headings, Column A as project name, Range B2:D4 as the data range. For simplicity I've got these in the same worksheet, in columns H:N, with H1=Joe, I1=mary etc.

Put this formula in the first cell (H2):

=IFERROR(INDEX($A$2:$A$4,SMALL(IF($B$2:$D$4=H$1,ROW($B$2:$B$4)-MIN(ROW($B$2))+1),ROW(1:1))),"")

Make it an array formula by pressing CTRL + SHFT + ENTER while the cursor's in the formula bar. Drag the formula right to the last column. Drag the formula down the rows (same number of rows as projects would be the maximum needed).

Match across multiple columns and return multiple values?

1 Answers