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


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.

Thank you in advance 😀


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!