How to use INDEX, MATCH and MAX to display newest form entry if cells match
Here's the setup
Sheet 1 Columns:
Created [standard] | Name | Form Entry
Sheet 2 Columns:
Name | Form Entry Display
MATCH Name:Name and take the Form Entry with the most recent Created i.e. the newest entry
Ideas?
Best Answer
-
Hi @mwiggins ,
There may be a more consolidated way to do this, but my preferred method would be to create a helper, Date column in the second sheet. In this column you could find what the Max Date is for that Name:
=MAX(COLLECT({Sheet 1 Created Column}, {Sheet 1 Name Column}, Name@row))
Then you could use a JOIN(COLLECT formula to return the Form Entry name, based on that Max Date and the Name in your second sheet:
=JOIN(COLLECT({Sheet 1 Form Entry Column}, {Sheet 1 Name Column}, Name@row, {Sheet 1 Created Column}, [Max Date]@row))
Note that this would only work if the Name and Date will be unique per-row, otherwise it will join together all of the form entries from that same day.
Here are some Help Center articles I used: MAX Function / JOIN Function / COLLECT Function / Cross Sheet Formulas
Let me know if this will work for you!
Cheers,
Genevieve
Answers
-
Hi @mwiggins ,
There may be a more consolidated way to do this, but my preferred method would be to create a helper, Date column in the second sheet. In this column you could find what the Max Date is for that Name:
=MAX(COLLECT({Sheet 1 Created Column}, {Sheet 1 Name Column}, Name@row))
Then you could use a JOIN(COLLECT formula to return the Form Entry name, based on that Max Date and the Name in your second sheet:
=JOIN(COLLECT({Sheet 1 Form Entry Column}, {Sheet 1 Name Column}, Name@row, {Sheet 1 Created Column}, [Max Date]@row))
Note that this would only work if the Name and Date will be unique per-row, otherwise it will join together all of the form entries from that same day.
Here are some Help Center articles I used: MAX Function / JOIN Function / COLLECT Function / Cross Sheet Formulas
Let me know if this will work for you!
Cheers,
Genevieve
-
Works BETTER THAN EXPECTED - that Max Date column has the added benefit of showing us when certain names haven't entered anything yet because it comes up blank.
Thank you Genevieve! Expertly crafted design,
Mark
-
So glad to hear that it works for you! You're very welcome - I'm happy to help. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!