I have a table where column „A” is the name of a given project; column „B” is the ID of a participant working on the project, and Column „C” signifies the nationality of the participant.
I am analyzing the composition of project participants, and I have to list all the projects along with all the participants that are carried out through the collaboration of 2 selected countries.
So I need a function that
• Step1. - Based on my variables (nationality 1 and nationality 2) finds all those projects where these two countries are collaborating
• Step 2. –Retrieves all the participants of the projects where nationality 1 and nationality 2 are collaborating
• Step 3. – lists all the rows that contain the retrieved participant data
My solution involved filters and Vlookup functions – although it yielded results, it was really tiresome.
Is there a way to do this efficiently?
I also attach an excel spreadsheet to clarify what I mean.
Thanks for the help in advance.
Bookmarks