I am having trouble with a formula which should be fairly simple but can't figure it out and it's driving me nuts.

What I'm trying to do: I have two spreadsheets, the first is a list of locations that shows Pass/Fail against 40 Internet Providers. It tells me Pass/Fail so I know what is available for each location. All Headers/Titles/Internet Providers are in Row 1. All Locations are in Column A.

The 2nd spreadsheet is a list of each of the 40 Internet Providers, with all available speeds, pricing, etc. A1=Provider, B1=Service Type, C1= IP Type, D1=Download Speed, E1=Upload Speed, F1=Monthly Price. Column A lists each provider but the number of options varies by carrier. For example - ATT has 20 different options, Bell South has 4, Century Link has 30, Comcast has 6, etc.

I want to run a formula that will essentially replace the Pass/Fail cells with all available speeds, pricing, etc.

Any help is much appreciated.