IF CONTAINS INDEX MATCH Formula

Options

I would like to be able to create a report where I can see Activity A, Finish (of activity A), Report A, and Finish (of Report A) in separate columns. I don't want to use group functionality in Row Report as it becomes messy when pulling it multiple activities from 50+ sheets.

Hence, I would like to create two additional columns in each project plan: Report, and Report Date. I want to use a formula to pull all of my reports into the Report column to be in the same row as the associated activity. For example, Activity A is in row 4. I would like to Pull Report A to be under Report column in row 4.

As there are many possible activities, I'm thinking this would require an IF CONTAINS INDEX MATCH statement in the Report column. If Task Name contains the word "Activity A", then the full task name (e.g., Activity A) would be copied to Report column. And If Task Name contains the word "Activity B", then the full task name (e.g., Activity AB) would be copied to Report column.

I'm hoping that I could then use row report and filter to see Activity A and Report A in separate columns along with their associated Finish dates.

Any advice on what formula I can use? or any advice on a better way to do this?


Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!