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

Valerie Harris

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?

Jen Lange

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

Valerie Harris

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

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

Valerie Harris

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

Jen Lange

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

Valerie Harris

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

Jen Lange

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

Valerie Harris

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