Add MAX function to INDEX for latest status of Account in Reference Library


I have a Scorecard sheet that I want to pull the most recent "Customer 12-month outlook" based on the quarter and year in the "ScoreCard Name" associated with the specific Account name into a Reference Library sheet for that same Account name.

Screen shot from ScoreCard Sheet

Screen shot from Reference Library:

current formula: =INDEX({SFA_SFDC_Scorecard Range 2}, MATCH([Account Name]@row, {SFA_SFDC_Scorecard Range 4}, 0))

But doesn't pull the most recent value.

When adding MAX, receive an Error: #INCORRECT ARGUMENT SET

=INDEX({SFA_SFDC_Scorecard Range 2}, MATCH(MAX({SFA_SFDC_Scorecard Range 3}), [Account Name]@row, {SFA_SFDC_Scorecard Range 4}, 0))

Any guidance on adjusting formula is appreciated!


  • Katy H
    Katy H ✭✭✭✭✭✭

    Are you attempting to perform the MAX collection on the Scorecard Name column? If so, this won't work as you can only use the MAX on numbers or dates.

    If you are you would need a helper column to complete the formula, and you would use INDEX(COLLECT()) instead of INDEX(MATCH()).

    Katy Hall

    Head of Product Management

    ILLA Canna


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!