Looking for most recent date

06/24/21
Accepted

I have two sheets. The first is a tracker for up coming reoccurring tasks and the second is a copy of rows that have been completed.

In the first sheet I would like to collect the date from the "date" column in the second sheet if the "Combined" column values match that on the first sheet and if "Most Recent" is checked in the second sheet. I know that there has to be a way to do this but every formula I try to use gives one error or another when I try to add the most recent criteria. For reference I will include images of the two sheets below.

I know that it will probably involve an INDEX/MATCH (the second sheet could be reordered) but not sure beyond that. And I might just be blanking on something I know but if anyone can help that would be great!


This is the first sheet:

This is the second sheet:


Best Answer

  • Genevieve P.Genevieve P. admin
    edited 06/25/21 Accepted Answer

    Hi @Kimberly Loveless

    If I'm understanding you correctly you want to find the most recent or MAX date, based on criteria in another column. In this instance, instead of an INDEX(MATCH, we can use a MAX(COLLECT function to find the Max date!

    The COLLECT function will filter down the Date range to only look at dates that are associated with the identical "Combined" value. Then the MAX function will find the MAX date in that range.


    Try this:

    =MAX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, [email protected]))


    Let me know if this works for you.

    Cheers,

    Genevieve

Answers

  • Genevieve P.Genevieve P. admin
    edited 06/25/21 Accepted Answer

    Hi @Kimberly Loveless

    If I'm understanding you correctly you want to find the most recent or MAX date, based on criteria in another column. In this instance, instead of an INDEX(MATCH, we can use a MAX(COLLECT function to find the Max date!

    The COLLECT function will filter down the Date range to only look at dates that are associated with the identical "Combined" value. Then the MAX function will find the MAX date in that range.


    Try this:

    =MAX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, [email protected]))


    Let me know if this works for you.

    Cheers,

    Genevieve

  • Kimberly LovelessKimberly Loveless ✭✭✭✭✭

    That works. Thank you @Genevieve P

    Its kind of annoying that I cant just leverage the check box that I am already using in the second sheet to mark the most recent.

  • Genevieve P.Genevieve P. admin
    edited 06/25/21

    Hi @Kimberly Loveless

    You could do that as well, no problem. There's lots of combinations of functions that can help you get to the same end-goal.

    If you want to go that route, you can use an INDEX(COLLECT instead of a MAX(COLLECT. Then one of your ranges and criteria will be the checkbox column and if it's checked!

    Like so:

    =INDEX(COLLECT({Date Column Sheet 2}, {Combined Column Sheet 2}, [email protected], {Most Recent Checkbox Column}, 1), 1)

    However this adds an additional range into your formula, so the MAX(COLLECT may be simpler. It's up to you!

    Cheers,

    Genevieve

Sign In or Register to comment.