I have been trying to resolve in Excel using VLOOKUP and INDEX/MATCH and am hitting a dead end.

Initial conditions are: Excel 2016 with no VBA and No Excel 365 capabilities (so I can't use the fancy array FILTER available in Excel Online)

I have a primary sheet with child sheets. Primary Sheet - MAIN

Child Sheet - DAILY

All I am trying to do is read if the columns DAILY, WEEKLY or MONTHLY are set to "Yes" - then populate the respective Sheet with the data (FirstName, LastName, Team, Email) - to automate the creation of the Child Sheet - DAILY.

If any changes are made in MAIN Sheet they should automatically update the DAILY/MONTHLY/WEEKLY Sheets if it is set to Yes.

I am aware that filters may work if I keep this in one sheet - I am deliberately trying to filter this data into another Sheet.

Any answers would be appreciated.

CodePudding user response：

So , this is what I have tried using INDEX(), AGGREGATE() & MATCH() Functions,

=IFERROR(INDEX(MAIN!$A$2:$G$6,AGGREGATE(15,6,(ROW(MAIN!$A$2:$G$6)-ROW(MAIN!$A$2) 1)/
((MATCH($E$1,MAIN!$E$1:$G$1,0)=(COLUMN(MAIN!$E$1:$G$1)-COLUMN(MAIN!$E$1) 1))*
(MAIN!$E$2:$G$6="Yes")),ROW(A1)),MATCH(A$1,MAIN!$A$1:$G$1,0)),"")