# INDEX/MATCH/COLLECT How to get most recent entry by person and by most recent date

Options
edited 06/29/23

Good afternoon,

I have been spinning my wheels trying to get my formula to work and scouring the community. Hope someone here can help :)

I have employees that need to checkout items and they record this by form. My want is to pull the most recent date based on name and whatever item they checked out.

Sheet A w/ the form has 3 columns - Name (Contact List), Item (text), Created (System Date)"{Create}"

Sheet B has - Name (Contact List), Item (text)

Column I need with a value is "Name of Last Person to Checkout" and that formula so far is:

=INDEX(COLLECT({Name}, {Item}, Item@row), MATCH(MAX(COLLECT({Create}, {Item}, Item@row)), {Create}), 0)

So the formula doesn't fully work because Bob and Joe are showing Invalid Values but Tom is displayed. My thought is I am missing a fundamental piece of function knowledge, syntax, or might need to wrap part of the formula into another function.

Tags:

• ✭✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

Hi @Needtoworksmart3r, I would use a RowID column instead of the Created By--it accomplishes the same task and it's simpler to work with numbers than date/time. You can then create a helper column (call it HelperColumn) in Sheet A to identify the lines you want with this formula:

=IF(COUNTIFS(Item:Item, Item@row, RowID:RowID, <=RowID@row)=1,"x","")

Then have your formula in Sheet B point to that helper column:

=INDEX(COLLECT({Name}, {Item}, Item@row), {HelperColumn}, "x"),1)

• Options

The sheet A is linked to the form so there will be multiple entries of the employees with also the same items but with different dates. Some items are checked out more than once per day so that is why I was using MAX with the created date (not created by).

• Options

@Lucas Rayala Do you know how I would incorporate the most recent entry by person by item. There will be many repeat entries and the only thing that will be different is the date.

• ✭✭✭✭✭✭
Options

@Needtoworksmart3r, I meant Created On, not Created By. Because the RowIDs are sequentially created, you can use them to track the first instance. My original post will do what you need.

• ✭✭✭✭✭✭
Options

Sorry, I’m having trouble writing my last comment and I realized you were asking for the most recent, and I gave you the formula for the earliest. Do what I said in the first post but but update this formula as follows (just change the greater than symbol to less than):

=IF(COUNTIFS(Item:Item, Item@row, RowID:RowID, <=RowID@row)=1,"x","")

• Options

@Lucas Rayala You the man! Thank you very much!

• Options

And just in case someone else finds this, a slight update to the formula for the extra ")"

=INDEX(COLLECT({Name}, {Item}, Item@row, {HelperColumn}, "x"), 1)

• ✭✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

To make use of your great idea of MAX(COLLECT({Created}, {Item}, Item@row)), you could make it work with modifications like these.😃

=INDEX(COLLECT({Name}, {Item}, Item@row), MATCH(MAX(COLLECT({Create}, {Item}, Item@row)), {Create}), 0)

Modified formula:

• =INDEX(COLLECT({Name}, {Item}, Item@row, {Create}, MAX(COLLECT({Create}, {Item}, Item@row))), 1)
• =JOIN(COLLECT({Name}, {Item}, Item@row, {Create}, MAX(COLLECT({Create}, {Item}, Item@row))))

• Options

Thanks! I was so close

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!