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

jcouncil

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

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()).