How can I use index match + either another index match or an IF statement to pull Data from multiple

Khadija Ali

How can I use index match + either another index match or an IF statement to pull Data from multiple sheets?

In my case I have 3 sheets:
Sheet 1 - Current Project List - consists information that track active projects. (each Project has a unique project code)
Sheet 2- Closed Project List - consists of previous years projects that have been archived.
Sheet 3 - Research Data Retention sheet - This is the sheet I want to create a formula for to pull Project code after I insert the name of a project in the project name column from either of the project list without breaking up my work due to yearly archival process.

I am current using this formula and only getting Data Populated only from one project list which the closed project list:

=INDEX({Closed Project_Project ID List}, MATCH([Project Name]@row, {Closed Project List Range 1}, 0))

What formula should I use?

Smartsheet Formula Linking.jpg