How to search for PARTIAL matches in this already complicated formula???

I'm working in multiple tabs, which I'm trying to connect. This is the situation:

Sheet A - Total overview of projects that are in production within a company
- relevant data: column B lists all projects; column C lists the employee thats working on the project mentioned in B.

Sheet B to Sheet D - Employees that are working on above projects. Each employee has their own sheet.
- relevant data: column A contains the following formula, which retrieves all projects the employee is working on according to sheet A:

=IF(NOT(ISNA(INDEX(SheetA!$B$2:$B$27;SMALL(IF(EXACT("John";SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27)-ROW(SheetA!$C$2)+1);ROW(SheetA!1:1)))))=TRUE;INDEX(SheetA!$B$2:$B$27;SMALL(IF(EXACT("John";SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27)-ROW(SheetA!$C$2)+1);ROW(SheetA!1:1)))));"")

So I figured this formula out, which works perfectly, but I've encountered a problem. If a project is handed over to a different employee and so a different name is entered in Sheet A, the project disappears from the first person to have worked on it. I want to be able to see all projects an employee has worked on, even when it's handed over to someone else. So, I figured I could fill names in Sheet A for example as "John + Rose" and change the formula to finding a partial match instead of an exact match. So whenever the name John is found somewhere in the data, regardless of the cell saying + Rose, it would still retrieve the project for John. But I can't figure out how to change the formula to do this.

Can anyone please help me? My brain isn't working anymore by now...

Thanks!

I'm working in a google spreadsheet, (mandatory by my company unfortunately), so I can't upload a sample workbook..

Is it not possible to adjust the formula to search for partial matches on a name, instead of exact matches?

Excel would still be able to know someone is linked if I add the second name to the first in Sheet A; for example "John + Rose". If the formula can then search on partial matches for "John" in John his sheet and the formula would search for partial matches for "Rose" in Rose her sheet, the project would still pop up in both sheets, instead of it disappearing from John and appearing at Rose her sheet. Right?

This isn't working.. I want to search for John within data such as "John" or "John + Rose" or "Rose + John" and if John is found in a cell in column C (resources), than it should return the project that is mentioned in the same row in column B. I've tried applying the "*" to the name that needs to be searched, but it doesn't seem to work.

Yes, it returned nothing, which is the return in the formula when the name has not been found in the array. (I preferred to see a blank cell instead of #N/A in case nothing is found)

Thanks, this is a lot less complicated indeed and works the same. The last suggestion doesn't work though, I don't really understand why I need the EXACT, but it was the only way I could get the formula to work. If I remove it, it returns an empty cell instead of a project.

Please find attached the test Excel. Hopefully this will help!

I've just uploaded a test Excel, I downloaded it from Google Spreadsheets.

Thanks, how do I activate the array on a Macbook? I thought using CMD+ENTER, but it doesn't work. Or is this different when using a Google spreadsheet?

Never mind, found it. But it isn't working, no results. No error, just no data is returned.

Can't check on this computer, don't have Excel at our company. But I will definitely check it tonight when I get home. Will get back to you tomorrow on this! Thanks so much for the help and your time!