I have a dynamic named range with 2 columns, Task (column A) and Resource (column B). (Sheet 1) Both column A and B are defined as a table and are each a named range [tbl]Assign, TskAssign and RscAssign

I would like to create a formula that would display all resources associated with a single task and all Task associated with a resource (sheet 2).


I would rather not use the auto filter as management will be updating these quite often as they frequently manage workloads and rearrange resources as new tasks arise.

Is it possible to accomplish this using a formula (I am not familiar with VBA)?
Excel-001.png