Troubleshooting Index/Collect Issue

Question

I have one sheet that houses all updates and comments for specific Releases (Sheet 1). I have a second sheet that lists every Release being worked on (Sheet 2). I am trying to pull only the most recent update from the Sheet 1 into Sheet 2.

I am able to pull over an update when only using one criterion (either the Release name or the most recent date), however, whenever I try to expand my formula to pull data based off two criteria, I get #UNPARSEABLE.

Is there an obvious piece my formula is missing?

=INDEX(COLLECT({Update}, {Release}, [Release Number]@row, [{Date}, MAX({Date})]), 1)

Thank you!

Paul Newcome · Answer

That is the equivalent of "No Match". This is most likely because the MAX({Date}) is not returning the max date that corresponds to [Release Number]@row.

Assuming new entries are made to the log sheet via form, the easiest way to manage this would be to have new entries submitted to the top of the sheet then use a standard INDEX/MATCH.

If you can't have new entries made at the top of the sheet, you can use a MAX/COLLECT like so:

=INDEX(COLLECT({Update}, {Release}, [Release Number]@row, {Date}, MAX(COLLECT({Date}, {Release}, [Release Number]@row))), 1)

Dan Drexel · Answer

Ah thanks for catching that! Unfortunately, the error returned changed to #INVALID VALUE. Definitely a step in the right direction but I'm not sure what value is causing the issue. Especially since using each criteria individually returns a result.

Any guesses?

Paul Newcome · Answer

It is an extra set of [Square Brackets]. Give this a try:

=INDEX(COLLECT({Update}, {Release}, [Release Number]@row, {Date}, MAX({Date})), 1)