#NO MATCH on INDEX COLLECT function

L J

Hello! I am working on a formula to pull an ID for the row that has the LatestDate, and a checkbox is ticked. As there may be more than one matching row with these parameters, FROM those I need to return the ID for the row with the most recent CreatedDate. This will yield a maximum of one result.

This is the formula that returns a #NO MATCH (Note that values in { } represent the ranges)

=INDEX(
COLLECT({ID},
{LatestUpdate}, MAX({LatestUpdate}),
{Checkbox}, 1),
MATCH(MAX(COLLECT({CreatedDate},
{LatestUpdate}, MAX({LatestUpdate}),
{Checkbox}, 1)
),
COLLECT({CreatedDate},
{LatestUpdate}, MAX({LatestUpdate}),
{Checkbox}, 1),
0))

Here is the data:

The value that should be returned is 018.

Thanks in advance for any insight!

LJ.

Paul Newcome

To be sure I understand…

You will have multiple rows checked off. You need to evaluate the [Created Date] for these checked rows and pull the [ID] from the most recently created of the checked rows?

How are new rows added to the sheet?

L J

Hi Paul! To answer your questions:

Yes, there are potentially multiple rows checked off.
I need to evaluate the [Created Date] for rows that are checked AND have the most recent [Latest Update] date. Then, I need to pull the [ID] for the row that has the greatest [Created Date] of those.

New rows are added to the sheet via an entry form (where people tend to backdate the [Lastest Update] date, creating a discrepancy with the Created Date).

Thanks for the reply!

Paul Newcome

In that case, are you able to work with the sheet being sorted opposite and having newest rows at the top of the sheet? If so, a standard INDEX/MATCH should work. Otherwise, you can use an INDEX/COLLECT to pull in all checked rows and then in the row number argument of the INDEX function, use a COUNTIFS to count how many rows are checked.

L J

Thanks for this! Swapping the sort, ignoring the 'Created Date' entirely and just returning the ID for the first row found might be an easy answer! I will try it out.