Assistance with Index, Match, Max, Collect

System

This discussion was created from comments split from: Index, Match, Max, Collect.

Todd O

I am trying to do the same. Here is my max collect formula in which I am using the helper column you suggested.

=MAX(COLLECT([Approved Until]:[Approved Until], Patient:Patient, @cell = Patient@row))

Now I am trying to make my Index Collect on another sheet. Here is what I have so far:

=IFERROR(INDEX(COLLECT({Auth Level}, {Date Helper Column}, Name@row, {Patient name}, 1)), "---")

I am receiving an incorrect argument error. Please assist

Paul Newcome

@Todd O You do not have a criteria for the {Date Range}, and your last range/criteria set is flipped around backwards.

Todd O

Thank you Paul. Please explain what you meant by not having a criteria for {Date Rage}. This is not a component of my formula.

Paul Newcome

@Todd O See in bold:

=IFERROR(INDEX(COLLECT({Auth Level}, {Date Helper Column}, Name@row, {Patient name}, 1)), "---")