Using Index, Match, Min, If, and Today in one formula

pterribilis

Hi All,

I'm new to Smartsheets but not new to Microsoft Excel and I'm struggling with trying to get this to work. Basically I'm trying to grab a text field after evaluating a date field. I got this to work using a basic index match.

=INDEX([Summary]1:[Summary]13, MATCH(TODAY(), [Status Date]1:[Status Date]13, 0))

But this doesn't cut the mustard. I want it to do the equivalent of this:

=INDEX(SummaryColumn,MATCH(MIN(IF(DateColumn>TODAY(),DateColumn)),DateColumn,0))

This way it grabs the closest (future) date to today. But I'm getting a #Invalid Operation or Parsing error when trying to use:

=INDEX([Summary]1:[Summary]13, MATCH(MIN(IF([Status Date]1:[Status Date]13 > TODAY(), [Status Date]1:[Status Date]13)),[Status Date]1:[Status Date]13, 0))

This might not be the correct syntax as I can't even remember how many variations I have used (removing parenthesis, adding commas, etc).

Any help is much appreciated.

Paul Newcome

You would need a MIN/COLLECT instead of a MIN/IF.

MIN(COLLECT([Status Date]1:[Status Date]13, [Status Date]1:[Status Date]13, @cell> TODAY()))