Return specific cell from specific row that matches logic

Hi,

I've tried a few ways to get this to work, but need some help please.

I have some data which is formatted as per the attached example file.

I want to pull through data into the viewing sheet (Sheet1) from the mailing tab. What formula will check first that the team name matches, then search all rows with that team name to see if the column header (Team Leader) for example, has a "Y" value. If both match, then return the full name (Column E) from mailing, into the viewing sheet.

The green cells are what I wish to auto-populated based on the data from the mailing tab.

Grateful for your help as always!

Hi,

In C3 and D3, use the following array functions and copy down

=INDEX(Mailing!$F$2:$F$4,MATCH(A3&"Y",Mailing!$A$2:$A$4&Mailing!$G$2:$G$4,0)) =INDEX(Mailing!$F$2:$F$4,MATCH(A3&"Y",Mailing!$A$2:$A$4&Mailing!$H$2:$H$4,0))

make sure to confirm an aray with CTRL, SHIFT and ENTER.