How to return value from another sheet based on X, Y & Z

[Hurts2Brown]

This is a rather complex problem that will require a combination of functions to achieve, but I believe is possible to achieve without VBA. Unfortunately it is so compelx for me I struggle to even describe it!

Best way I can think is to talk in terms of the example document I have attached. The contents within the yellow highlighted column from the "Ex" sheet are what I would like a formula to generate.

My concept of how formula would operate:
-In a given row use the corresponding value from "Team" column to locate sheet
-Use value from BOTH the corresponding "Position" column and "Game" column as search parameter.
-Among these rows that fit criteria, look for any non-zero values in the "TD?" column
-RETURN their corresponding "Quarter" value(s) as a comma separated string


Many thanks to anybody who even takes the time to look at this!

[oeldere]

Eagels E2=IF($D2=0,"",$C2)


After that I made an pivot table.


I16 on sheet ex

=IF(RIGHT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,B16:E16),"0,",""),0,""),1)=",",LEFT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,B16:E16),"0,",""),0,""),LEN(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,B16:E16),"0,",""),0,""))-1),SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,B16:E16),"0,",""),0,""))

See the attached file.

The yellows cells are the helpcolumns so you can (easier) understand the above formula.

In green the result.


After posting the file, I read you have Excel 2016. In that version maybe textjoin is not available, so you could not use this solution.