Help with INDEX/COLLECT with a MAX/COLLECT

Emily McClary · 01/29/25

I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using:

=INDEX(COLLECT({IA Assigned}, {RTO Queue ID}, [RTO Queue ID]@row, {Fully Executed Date}, MAX(COLLECT({Fully Executed Date}, {RTO Queue ID}, [RTO Queue ID]@row))), 1)

This is working, except it doesn't take into account when Fully Executed Date is blank. How can I incorporate the data when the Fully Executed Date is blank?

Paul Newcome · 01/30/25

Do you want to include or exclude rows where that date is blank?

Emily McClary · 01/30/25

I would like to include rows where the date is blank.

Paul Newcome · 01/30/25

Ok. Try this:

=INDEX(COLLECT({IA Assigned}, {RTO Queue ID}, [RTO Queue ID]@row, {Fully Executed Date}, OR(@cell = "", @cell = MAX(COLLECT({Fully Executed Date}, {RTO Queue ID}, [RTO Queue ID]@row)))), 1)

Emily McClary · 01/31/25

Thank you for your response. I am now getting #NESTED CRITERIA. Any additional thoughts?

Paul Newcome · 01/31/25

In that case you will need to insert a helper column that houses the MAX/COLLECT, and then you would reference this column in the OR function.

Emily McClary · 01/31/25

Ok, I have successfully created a helper column with the formula below:

=MAX(COLLECT({Fully Executed Date}, {RTO Queue ID}, [RTO Queue ID]@row

Now I am questioning whether the Helper Column should reside on the original sheet or the sheet I am referencing (or does it matter?)

Also, from the current formula,

=INDEX(COLLECT({IA Assigned}, {RTO Queue ID}, [RTO Queue ID]@row, {Fully Executed Date}, OR(@cell = MAX (COLLECT({Fully Executed Date}, {RTO Queue ID}, [RTO Queue ID]@row)))), 1)

Where/How should reference the Helper Column?

=INDEX(COLLECT({IA Assigned}, {RTO Queue ID}, [RTO Queue ID]@row, {Fully Executed Date}, OR(@cell = "", @cell = [Helper Column A]@row))), 1)

I know this isn't 100% correct as I am receiving #INCORRECT ARGUMENT.

Thank you for all your help & patience 😀

Paul Newcome · 02/03/25

The helper column goes in the same sheet as the INDEX/COLLECT.

One of the three grouped closing parenthesis there before the 1 should be removed.

Formulas and Functions

Formulas and Functions

Help with INDEX/COLLECT with a MAX/COLLECT

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days