Return Value Based Off Of MAX Date, With Specific Criteria

MichaelNewkirk

Hey guys, I'm sure this is easy/right under my nose, but any help would be appreciated.

I have a master sheet that receives multiple evaluations for four different employees from their supervisors. Each of the four employees will have hundreds of evaluations sent to this sheet, with the date, their name, and their score in respective columns.

In a Sheet Summary field, how can I retrieve the most recent evaluation score for a specific employee, based off of the most recent date? Each employee is searchable, i.e "John Smith", because this sheet will only house evaluations for the same four employees.

Any help would be tremendously appreciated!

Genevieve P.

Hi @MichaelNewkirk

You can do this by using a combination of the INDEX( COLLECT and MAX functions!

Try something like this:

=INDEX(COLLECT([Score Column]:[Score Column], [Name Column]:[Name Column], "John Smith", [Date Column]:[Date Column], MAX(COLLECT([Date Column]:[Date Column], [Name Column]:[Name Column], "John Smith"))), 1)

Here's how this works:

=INDEX(

COLLECT(Column to bring back,

First Column with Names, "Name",

Date Column, Max Date for that Name)))

, First matching row)

Let me know if that makes sense and works for you.

Cheers,

Genevieve

Genevieve P.

Hi @MichaelNewkirk

You can do this by using a combination of the INDEX( COLLECT and MAX functions!

Try something like this:

=INDEX(COLLECT([Score Column]:[Score Column], [Name Column]:[Name Column], "John Smith", [Date Column]:[Date Column], MAX(COLLECT([Date Column]:[Date Column], [Name Column]:[Name Column], "John Smith"))), 1)

Here's how this works:

=INDEX(

COLLECT(Column to bring back,

First Column with Names, "Name",

Date Column, Max Date for that Name)))

, First matching row)

Let me know if that makes sense and works for you.

Cheers,

Genevieve

MichaelNewkirk

Genevieve, thank you! That absolutely did the trick, and the breakdown of how it worked helped a lot as well. Thank you so much!

Genevieve P.

Wonderful! I'm glad I could help 🙂

CARO885

Hello, I have a similar issue where I need to return the most recent entry based on Action Type :monthly inventory and Name.

So I need to know what was the latest "Monthly Inventory" entry for each technician, I need to return the monitors, tool kits, etc

Genevieve P.

Hi @CARO885

Were you able to adapt the formula above for your sheet? Here's what it would look like with your column names:

=INDEX(COLLECT(Monitors:Monitors, [Technician Name]:[Technician Name], "John Smith", Date:Date, MAX(COLLECT(Date:Date, [Technician Name]:[Technician Name], "John Smith"))), 1)

If you're looking across sheets:

=INDEX(COLLECT({Monitors Column Reference}, {Technician Name Column}, "John Smith", {Date Reference}, MAX(COLLECT({Date Reference}, {Technician Name Column}, "John Smith"))), 1)

Cheers,

Genevieve