How to return a value from the range of cells with the most recent date.

I have a sheet like the one above for each of my 100 employees. I want to be able to run a report on all these 100 sheets such that I can see the most recent date of an employee review and what the expectations for the next period will be. I am trying to use row 6 (highlighted in yellow) as the row to pull the most recent date and expectations for next period. I have read the tutorials on index, match, collect, max etc and been able to get the most recent dates using the max function. But I have not been able to get the words from the expectations column using the any combination of the index match and collect to work. Can someone hlep?

Best Answers

Answers

  • Jen Lange
    Jen Lange ✭✭✭✭✭

    Hi @Valerie Harris. It's hard to develop a formula without access to the sheet(s) for reference and testing; however, based on what you've stated and your screenshot, it would seem that you could just use a VLOOKUP to acquire the expectations for the next period.

    If you know the employees name, the date of the last review and the sheet that information is stored within, those values should satisfy a VLOOKUP. Only hurdle is that you'll need to move the Expectations column to the right of your date columns or add a reference column to the front of the sheet to satisfy the lookup.

    Let me know if you need more instruction to develop this formula.

    -Jen

  • I was hoping to use index and Max so I could keep the columns the way they are.

  • Jen Lange
    Jen Lange ✭✭✭✭✭

    Okay. Thanks for the feedback, Valerie. Let me try to rework the formula today and get back to you.

  • Thanks Jen. I can share the sheet with you if that would be helpful. I

  • Jen Lange
    Jen Lange ✭✭✭✭✭
    Answer ✓

    @Valerie Harris. Yes, that would be super helpful. Thank you.

  • Valerie Harris
    Answer ✓

    I figured it out @Jen Lange. This is the formula I used. =INDEX(Celebrations:Celebrations, MATCH(MAX([Date of employee/Manager review]:[Date of employee/Manager review]), [Date of employee/Manager review]:[Date of employee/Manager review], 0)). Where the column celebrations contains the text information I want for the cell, on the most recent date of a manager review column. Works like a charm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!