How can I use a function to pull dates from Sheet A to Sheet B?

kk119

I have a master sheet of dates for multiple programs and projects. The layout of the sheet is:

Program | Project | Start Date | End Date

Each program is divided into hierarchies as such:

Program A

--Program A-1

----Program A-1 | Project A | Start Date | End Date

----Program A-1 | Project B | Start Date | End Date

----Program A-1 | Project C | Start Date | End Date

--Program A-2

----Program A-2 | Project A | Start Date | End Date

----Program A-2 | Project B | Start Date | End Date

----Program A-1 | Project C | Start Date | End Date

Program B

--Program B-1

----Program B-1 | Project A | Start Date | End Date

...and so on. Unfortunately the exact data is sensitive and cannot be shared so I hope this makes sense.

What I am looking to do is pull all Project A start and end dates from all sub-Programs into a new sheet that will automatically update if the master sheet is updated. So the end result I am looking for is:

Program A-1 | Project A | Start Date | End Date

Program A-2 | Project A | Start Date | End Date

Program B-1 | Project A | Start Date | End Date

I have been trying to use the INDEX formula to achieve this, but as a Smartsheet beginner it's a little above my head. I appreciate any help you can provide. Thank you!

Genevieve P.

Hi @kk119

Can I clarify if you already have the Program listed in this second sheet? An INDEX(MATCH formula needs something unique in the other sheet to match against so it knows what data to bring back.

So in your formula sheet, you'll already want to populate one column down with:

Program A-1 

Program A-2

Program B-1

Does that make sense? Now that you have a unique value to match, you can use the INDEX(MATCH formula like so:

=INDEX({Column with Value to Return}, MATCH(Program@row, {Program Column}, 0))

Here's more information: Lookup one cell using a matching value

Cheers,

Genevieve