How do I create a Index/Match formula w/ a condition across sheets?

PfossRiv04

I have a sheet with a bunch of site location data. This sheet includes a check box column showing if the site is Open (Checked = Open).

I have another Project sheet where I want to pull in only the OPEN Sites and populate 3 columns from the Location data (Site Name, Region, & Size).

I am having a hard time finding a formula that will work to pull only the data I need across the whole sheet. I need it to pull all 19 Open records from the Location Sheet and have only been able to get it to pull the first one. In order to get it to pull the next row down I have to go into the formula and edit the Row & Column manually. I need the Project sheet to update when new locations are opened.

Please help!

PfossRiv04

I was able to get it to return one location, based on the condition but I don't want to have to manually do this formula all the way down the sheet, as well as in the 2 other columns I want data.

=IF({Site Directory Range 2} = 1, {Site Directory Range 1})

Column Headers in Location data sheet are: Site_Status, Location_Name, Site_region, & Site_Subtype

Column Headers in project sheet that I want to match (in order) are: Site, Region, Size - Again this sheet will only show sites that are Open.

Any Thoughts on how to add indexing to this @Paul Newcome or @Andrée Starå ?

Paul Newcome

Are you able to provide screenshots for reference?

Why are you pulling this data into a sheet instead of using a report?