Dynamic Array Challenge - Filter from multiple tables?

[davidudow]

Hi,
Trying to pull data from three different tables.
This is a dynamic array challenge, though could potentially be achieved in a pivot table, which would be fine too.

Referencing the sample file, I would like to populate the lower table based on the values from the three upper tables. Ideally this can be accomplished with one dynamic array formula in cell B15. I have been experimenting with FILTER & INDEX commands, but I keep getting stuck trying to put the results of two filters into one dynamic array in B15.

Ideally...

B15 = According to B12 (the chosen PropMgr), Show a list of all matching Buildings. Beside each Building, show the Route name, Clearance for that building, AlternateRoute (if applicable) PrimaryRoute (if applicable), Name and Status.

Or explained another way...
B15 = Step 1 - Look in Cell B12 (Atlas)
Step 2 - Look in upper middle table for all rows that contain Atlas (RED highlights)
Step 3 - For each matching row in the middle table show the Building, Route & Clearance (YELLOW highlights).
Step 4 - For each matching Route, find the matching Name (GREEN highlights)
Step 5 - For each name, show the matching Level (PURPLE highlights)

Of course, all of the tables will grow so I'd like this to happen dynamically.
I'm happy to use any intermediate tables or whatever else is necessary.

Sample file attached.
Here's a photo with arrows showing where everything comes from.
SampleData.png

Thanks for any suggestions.
David

[DJunqueira]

I end up building relations with Power Query and filtering with Slicer.

[davidudow]

I have to say this is fascinating. Now of course my challenge is replicating what you have done in my actual data, which after a few minutes of looking is going to be a challenge.
I'm not sure of the protocols for this, but is there anyway to upload something closer to my actual workbook so I can see this happen in reality? I'm also more than happy to compensate for the work (however is best, I'm not clear, perhaps moving to commercial side?).

[DJunqueira]

Do you have some knowledge about Power Query? It is very easy.