trouble with a combination of Index Collect

DavidB

This particular challenge is eluding me and even Smartsheet's formula AI. Hopefully someone in the Community can help.

For the matrix below, I need a formula that will evaluate the matrix, and return the Review Status for the latest date corresponding to the Checkpoint "Pricing Commit".

BTW, the row numbers (even though not shown here) are 121-127

The formula I have created (and even recommended by SS AI), is below. Unfortunately it returns an invalid value. My destination cell, Checkpoint, and Review Status are text fields and single select dropdowns. Review Date is of course a date field.

=INDEX(COLLECT([Review Status]121:[Review Status]127, Checkpoint121:Checkpoint127, "Kickoff", [Review Date]121:[Review Date]127, MAX([Review Date]121:[Review Date]127)), 1)

Matt Lynn ACT

@DavidB I used full column references rather than the 127 etc row numbers but if you need it just type those same numbers back in.

=INDEX([Review Status]:[Review Status], MATCH(MAX(COLLECT([Review Date]:[Review Date], Checkpoint:Checkpoint, "Pricing Commit")), [Review Date]:[Review Date], 0))

Matt Lynn ACT

@DavidB I used full column references rather than the 127 etc row numbers but if you need it just type those same numbers back in.

=INDEX([Review Status]:[Review Status], MATCH(MAX(COLLECT([Review Date]:[Review Date], Checkpoint:Checkpoint, "Pricing Commit")), [Review Date]:[Review Date], 0))

DavidB

@Matt Lynn ACT . Hello Matt, and a thousand thank you's!!! The final puzzle I am trying to achieve is actually quite complex (more than depicted here). Seeing your solution made me realize I should create several helper columns as stepping stones, and then build one final formula simply combining the helper columns in simpler logic. I'm pleased to say my final solution now finally works (after wrestling with it for about a week)! I appreciate your expertise in helping to build my puzzle.

Matt Lynn ACT

@DavidB any time my friend! Happy to help. You know where to find me. :)