How to use INDEX, MATCH and MAX to display newest form entry if cells match

mwiggins

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?

Genevieve P.

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

Genevieve P.

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

mwiggins

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

Genevieve P.

So glad to hear that it works for you! You're very welcome - I'm happy to help. 🙂