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

Please Login or Register  to view this content.

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.