# Return Value Based Off Of MAX Date, With Specific Criteria

Options
edited 09/06/22

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!

Options

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

Options

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

• Options

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

Options

Wonderful! I'm glad I could help 🙂

• ✭✭
Options

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

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!