Bring a list of cells where column in 2nd Sheet matches a cell value in first sheet

Hello,

In a quandary here. I have a DistanceCalutatorByZip sheet that has a Site ID column. I have another sheet that houses all those site ID's. I would like to populate the Site ID column from the TI (sheet that houses all the Site IDs for all markets) based on Market (listed in Cell E2). Markets can have up to 2000 sites. The end result would be when the user selects the market in Cell E3, the entire list of sites in market CHI populate in Col C.


User changed entry: E2 on DistanceCalutatorByZip tab
Site list source: TI Tab
Market in Col A
Site ID in Col B
Destination: starts at Q8 on DistanceCalculatorbyZip tab

I feel that this could be accomplished using spill formulas, however as I don't have those in my version I'll offer a power query based solution.
1. Convert the data on the TI sheet into an Excel table (tbl_SiteID)
2. Convert cells E1:E2 on the DistanceCalculatorByZip sheet into an Excel table (tbl_MarketSelection)
3. Make a connection only to tbl_MarketSelection
4. Use the following power query editor code to produce the table in column N:

Please Login or Register  to view this content.

Note that after the text in cell E2 is changed the table in column N will need to be refreshed.
Here is a link to a video tutorial that may better explain the process.
Let us know if you have any questions.

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.