Returns value with matching data (cross sheets)

firestorm ✭✭✭
edited 07/15/21 in Smartsheet Basics

Hello, I have a sheet to receive project requests with reference, descriptions, and team members in 2 columns, the main person-in-charge, and other team members (multiple contacts). There is also a project status column.

I have a separate sheet for respective project members to update on the status, e.g. Jose. I would like to populate the "Reference" and "Description" in his sheet if his name appears in either of the 2 columns, and if the project is not "closed" in the status column.

How do I get all the projects if Jose appears in multiple rows?

Appreciate your help.