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

Adam Murphy

Since your formula works only if the record is in the closed sheet, you can use an iferror function to check the current project sheet if the record is not found in the closed sheet, something like =IFERROR(INDEX({Closed Project_Project ID List}, MATCH([Project Name]@row, {Closed Project List Range 1}, 0)), INDEX({Current Project_Project ID List}, MATCH([Project Name]@row, {Current Project List Range 1}, 0))). Would that work?

Adam Murphy

Did you define the 2 new cross-sheet references?

Adam Murphy

Since your formula works only if the record is in the closed sheet, you can use an iferror function to check the current project sheet if the record is not found in the closed sheet, something like =IFERROR(INDEX({Closed Project_Project ID List}, MATCH([Project Name]@row, {Closed Project List Range 1}, 0)), INDEX({Current Project_Project ID List}, MATCH([Project Name]@row, {Current Project List Range 1}, 0))). Would that work?

Khadija Ali

@Adam Murphy I tried and it is not pulling the data from the current project list yet! It is showing No Match!
Here are some of the Snippets:

Smartsheet Formula 3.jpg

Smartsheet Formula 2.jpg

Adam Murphy

Did you define the 2 new cross-sheet references?

Khadija Ali

@Adam Murphy Thank you! I got it.