Index Match or Max Collect Formula?

Mary Royston

I have a form that people can use to submit an "Updated Due Date". This can be Approved or Denied by a supervisor. Once approved it moves to an Approved Sheet with the Move Row automation. Multiple entries can be made for the same project.

I have a main sheet that pulls all the data together, and I would like the Updated Due Date to update as approved entries are made. If there is not approved updated due date, the value is the initial due date. Right now I am using and If error with an Index Match formula to pull from the Approved sheet, something like... =Iferror(Index(UpdatedDueDate, Match(ProjectNumberColumn, Project Number@row, 0), InitialDueDate@row). The only thing is, it is not pulling in the latest entries for updated due date, unless I manually sort the Approved sheet everyday by the Modified Date column.

When I try to add in a Max Collect formula, something like, =IFERROR(MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), Initial Due Date@row)I am running into trouble with it not putting the Initial Due Date.

How can I get the Updated Due Date with the most recent Modified Date, that Matches by the Project Number?

Genevieve P.

Hi @Mary Royston

What about using an IF statement instead of IFERROR? Check to see if there is no Max Due Date, and if the MAX is 0 (meaning there is no Max), return the Initial Due Date. If not, return the original MAX(COLLECT.

Something like:

=IF(MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)) = 0, Initial Due Date@row, MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)))

It means repeating your MAX(COLLECT - once to find out if there is anything in that column for your criteria, then once to actually return the value if it's not blank. Let me know if this works for you!

Cheers,

Genevieve

Mary Royston

Hey @Genevieve P.

Thank you, I really appreciate you taking the time to answer! That almost worked, but I kept getting the #INVALIDOPERATION error if it is a project number that does actually have an updated due date. If it did not have an updated due date, it put in the Initial Due Date.

So with your help I was able to come up with the working formula which ended up being, =IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row).

Thank you again :)

Mary Royston

@Genevieve P.

Thank you again for your help :) With the new formula I kept getting Invalid Column Value, even tho all the column types were matching. But I used the new formula you gave me and then another one you provided on a different discussion and was able to come up with the right formula!! Thank you for answering everyones questions :) The final formula turned out to be:

=IF(HAS({ProjectNumberColumn}, ProjectNumber@row), INDEX(COLLECT({UpdatedDueDateColumn}, {CreatedDateColumn}, MAX(COLLECT({CreatedDateColumn}, {ProjectNumberColumn}, Projectnumber@row))), 1), [Initial Due Date]@row)

Genevieve P.

Hi @Mary Royston

What about using an IF statement instead of IFERROR? Check to see if there is no Max Due Date, and if the MAX is 0 (meaning there is no Max), return the Initial Due Date. If not, return the original MAX(COLLECT.

Something like:

=IF(MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)) = 0, Initial Due Date@row, MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)))

It means repeating your MAX(COLLECT - once to find out if there is anything in that column for your criteria, then once to actually return the value if it's not blank. Let me know if this works for you!

Cheers,

Genevieve

Mary Royston

Hey @Genevieve P.

Thank you, I really appreciate you taking the time to answer! That almost worked, but I kept getting the #INVALIDOPERATION error if it is a project number that does actually have an updated due date. If it did not have an updated due date, it put in the Initial Due Date.

So with your help I was able to come up with the working formula which ended up being, =IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row).

Thank you again :)

Genevieve P.

Wonderful! I'm glad you were able to find a solution that worked. 🙂

Mary Royston

@Genevieve P.

Hey! I am hoping you might be able to help me again. So, the formula worked, except for it is finding the Max Updated Due Date, instead of using the Latest entry, which would be the Max Created Date. Sometimes the updated due date is earlier, which is why I need to base the Max from the Created Date column, so it captures the latest entry.

How can I make my formula give me the Updated Due Date, that Matches the Project Number, with the most recent Created Date? I tried to add an additional criteria range to the collect, but I keep getting unpareseable error. Would love any help or suggestions!

I tried: =IF(HAS({ProjectNumbercolumn}, projectnumber@row), COLLECT({UpdatedDueDateColumn}, {ProjectNumbercolumn}, projectnumber@row, {CreatedDatecolumn}, Max({CreatedDatecolumn})), [Initial Due Date]@row))

Genevieve P.

Hi @Mary Royston

It's finding the Updated Due Date as that's the first range listed after MAX(COLLECT in your original formula. We just need to swap that out to be your Created Date column, instead!

Original:

=IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row)

New:

=IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(CreatedDateColumn, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row)

Cheers,

Genevieve

Mary Royston

@Genevieve P. yes, but wouldn't that give me the created Date instead of the updated Due Date? I would still like it to display the value of the Updated Due Date, but with the most recent Created Date.

Here is an example, see screenshot below: For Project 3223, the answer should be 4/11/22, based on the newest created date.

Here is what is submitted for the project:

Here is what I pull up with the Formula:

Genevieve P.

Hi @Mary Royston

Thank you for clarifying! I understand now; you'd like to use the MAX Created Date as another condition/filter to bring back the Updated date.

You were close with your previous formula then. However when we're looking for the MAX date, we'll need to use a MAX COLLECT again because we're looking for the MAX date of the same Project Number.

MAX(COLLECT({CreatedDatecolumn}, {ProjectNumbercolumn}, projectnumber@row))

Try something like this:

=IF(HAS({ProjectNumbercolumn}, projectnumber@row), COLLECT({UpdatedDueDateColumn}, {ProjectNumbercolumn}, projectnumber@row, {CreatedDatecolumn}, MAX(COLLECT({CreatedDatecolumn}, {ProjectNumbercolumn}, projectnumber@row))), [Initial Due Date]@row)

Cheers,

Genevieve

Mary Royston

@Genevieve P.

Thank you again for your help :) With the new formula I kept getting Invalid Column Value, even tho all the column types were matching. But I used the new formula you gave me and then another one you provided on a different discussion and was able to come up with the right formula!! Thank you for answering everyones questions :) The final formula turned out to be:

=IF(HAS({ProjectNumberColumn}, ProjectNumber@row), INDEX(COLLECT({UpdatedDueDateColumn}, {CreatedDateColumn}, MAX(COLLECT({CreatedDateColumn}, {ProjectNumberColumn}, Projectnumber@row))), 1), [Initial Due Date]@row)

Genevieve P.

Hi Mary,

I'm so glad you were able to find the right solution! 🙂