#NO MATCH on INDEX COLLECT function

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.
Answers
-
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?
-
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!
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!