Show corresponding cell based on another formula
I have a current sheet where I want to surface the next major milestone and next major milestone date. Currently there are a list of tasks and dates and a checkbox to identify that task as a "major milestone".
I have a formula written to show the next upcoming milestone date, but need to, based on the date referenced, surface the corresponding milestone description. For example, if the formula surfaces that the next major milestone is November 1, 2021, I want to also show that it is "Identify Key Stakeholders" that is occurring on November 1, 2021 (and that description is currently in the same row as the original date listing. Does that make sense?
Answers
-
Hey @Sean_Dell
An Index/Match will do the trick for you. This has the syntax of =INDEX(data range you are trying to surface, MATCH(a reference, range that we can match the reference to, sort option)). Your question didn't mention if this was all taking place in a single sheet, or if we were referencing data across 2 sheets. I will assume single sheet. This formula would go in a different column than the milestone date
=INDEX([Task Description column]:[Task Description column, MATCH([Next Milestone Date]@row, [Task Date column]:[Task Date Column], 0))
Be sure to change column names to your actual names. If the data was cross sheet referenced, you will need to create and insert these references in place of the column ranges.
For more information on Index/Match, see this post
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!