Show corresponding cell based on another formula

Sean_Dell

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?

Kelly Moore

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