It just needs some ranges extended.
In B3 of 'Members Data' array enter this formula and fill down. (Same as before ... commit from edit mode with Ctrl + Shift + Enter)
Formula:
=IFERROR(INDEX('Team Members'!$A$2:$N$150,SMALL(IF(TRIM(CLEAN(SUBSTITUTE('Team Members'!$B$2:$K$150,CHAR(160),"")))<>"",ROW('Team Members'!$B$2:$K$150)-MIN(ROW('Team Members'!$B$2:$K$150))+1),ROW()-ROW($3:$3)+1),MATCH('Members Data'!B$1&"*",'Team Members'!$A$1:$N$1,0)),"")
Then in A3 and filled down this regular formula.
Formula:
=IFERROR(INDEX('Team Members'!$B$2:$K$150,MATCH(B3,'Team Members'!$A$2:$A$150,0),INDEX(ROW($B$3:$B$151)-MIN(ROW($B$3:$B$151))+1,ROW()-ROW($3:$3)+1)-MATCH($B3,$B$3:$B$151,0)+1),"")
Then in C3 this regular formula filled down and across column D.
Formula:
=IFERROR(INDEX('Team Members'!$A$2:$N$150,MATCH($B3,'Team Members'!$A$2:$A$150,0),MATCH('Members Data'!C$1,'Team Members'!$A$1:$N$1,0)),"")
The modified headers are the same as my previous with the addition of the Eligibility column.
Bookmarks