How match a value from a second sheet to a correlated date

JBerm
JBerm
edited 01/19/22 in Formulas and Functions

I have two sheets, a primary sheet and a second sheet I'm using as a metric sheet.

Sheet 1 (Primary sheet)


Sheet 2 (Metric sheet)


Some background. The first sheet uses a formula in the "Next Action Item Column" =IF([Year of Project]@row = "1st year", MIN(COLLECT({Key Dates (AC)}, {Key Dates (AC)}, ISDATE(@cell), {Key Dates (AC)}, @cell >= TODAY(), {Key Dates (AC)}, <=TODAY(5))), IF([Year of Project]@row = "2nd year", MIN(COLLECT({Key Dates (AC2)}, {Key Dates (AC2)}, ISDATE(@cell), {Key Dates (AC2)}, @cell > TODAY())))). The formula looks at the "Year of Project" column in Sheet one and then pulls the data from the metric sheet (sheet 2) and effectively posts that value in the "Next Action Item Date" Column, and works just fine.

Now what I'm trying to do is take the type of update, as seen in sheet 2 i.e., "1st year: Type of Update" column, and have that value populate in the "Next Action Item" column on sheet one matching the date. I'm some what lost as to the best approach, whether that be a vlookup or a match formula.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!