INDEX when a cell has multiple text and return a value

[evolive]

Hi Everyone,

I have this formula that I got working for what I want and now I am trying to add another level of complexity to it.

Below is the formula I use to search for a Person's name and if that person's name matches then it will show the task assigned to that person.

=IFERROR(INDEX(Table2[Projects],SMALL(IF(Table2[Owner]=B$1,ROW(Table2[Owner])-10,""),ROWS(C$3:C3))),"")

This formula works well for unique names, but imagine I have two people assigned to it (e.g., "Person 1, Person 2" or "Person 1 Person 2"). The formula above cannot differentiate. I tried to search for string text, find, match but cannot incorporate into above.

I do not want to create a separate cell where I match Person 1, Person 2 with the task as I feel that is unnecessary. I would like to avoid macros or vb. below is basically what I would like to accomplish
=IFERROR(INDEX(Table2[Projects],SMALL(IF(Table2[Owner]=return table2[project] if cell contains any of these names,ROW(Table2[Owner])-10,""),ROWS(C$3:C3))),"")

Any help would be appreciated.

If you have the time, also how to bring over the cell formatting (fill color) over to where it's being called out. From what I have searched VB is the only method.

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E
1	Project	Owner		Owner	Projects
2	Pro1	Person1		Person2	Pro3
3	Pro2	Person3			Pro6
4	Pro3	Person1, Person2			Pro8
5	Pro4	Person4
6	Pro5	Person5
7	Pro6	Person2, Person4
8	Pro7	Person3
9	Pro8	Person2
10	Pro9	Person5
Sheet

 

[evolive]

Hi AhoyNC,

This worked out great. Thank you very much!

New post later to copy over cell (fill color) formatting.

Thank you again AhoyNC.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.