Matching Neighbor Cells or Rows to Most Recent Date

Nick_Frisbee

Hey All,

Background: Im trying to build a fluid dashboard that tracks information coming in from a form. This form is then routed to several sheets where I break it down further based on certain criteria. I am looking for a way to report on the most recent metrics in the given sheet using the sheet summary feature to make it easy to find and add to a dashboard.

Problem: I need a formula that reports on only the most recent row that has been added to the sheet. I think i can do this by some mixture of index and collect but I'm not sure. Could anyone help out with a formula that would return the matched value with the most recent date in the sheet for the columns "Scouting Health and "Field Rating" please? Is there a way to pull in that info with the date it corresponds too (in this case maybe using max([date]:[date]).I would like to keep them as symbols if possible. First picture is the max formula im using to get the most recent scouting date. Thanks so much!

scouting date.png

sshelp.PNG

Paul Newcome

You could use an INDEX/MATCH based on the date.

=INDEX([Column Name]:[Column Name], MATCH(MAX([Date Scouted]:[Date Scouted]), [Date Scouted]:[Date Scouted], 0))

Paul Newcome

You could use an INDEX/MATCH based on the date.

=INDEX([Column Name]:[Column Name], MATCH(MAX([Date Scouted]:[Date Scouted]), [Date Scouted]:[Date Scouted], 0))

Nick_Frisbee

Thanks so much @Paul Newcome I knew I was missing something!

Paul Newcome

Happy to help. 👍️