Index/Match/Max Function Issues

@Paul Newcome @Andrée Starå


I am having issues matching a cell value from my project status column. My goal is to reference my most recently updated (max modified cell) data entry and return that value in the selected cell.


My current formula for reference:

=INDEX([Project (Schedule) Status]3: [Project (Schedule) Status]54, MATCH ([Project (Schedule) Status @row], MAX([Modified3] : [Modified54])))


The value returned is #UNPARSEABLE


I need direction on this issue I have no clue where my issue arises.



See attached for picture reference, the columns I am working with are the two furthest to the right


Answers

  • Summer
    Summer ✭✭✭
    edited 02/23/22

    Hi @dc13

    I took a slightly different approach and went with an IF formula.

    =IF([Modified]@row =MAX(Modified]:[Modified],[Project (Schedule) Status)@row,"No Match")

    If Modified date in that row is equal to the Maximum date in the Modified row than return the RYG ball in the Project Schedule Status for that date. If false display "No Match"

    You're going to want to limit the Modified range so it does not include the row you're placing the formula in.

    If this approach doesn't work for you, please mark as unanswered and others can have a look.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 02/23/22

    =INDEX([Project (Schedule) Status]3: [Project (Schedule) Status]54, MATCH ([Project (Schedule) Status @row], MAX([Modified3] : [Modified54])))


    Your main issue is with your match statement.

    MATCH ([Project (Schedule) Status @row], MAX([Modified3] : [Modified54])

    match project status @row is to be matched on the max, but max is going to return a single value. Instead, I believe you want

    MATCH(MAX(Modified3:Modified54),Modified3:Modified54,0

    so your formula would be

    =INDEX([Project (Schedule) Status]3: [Project (Schedule) Status]54, MATCH(MAX(Modified3:Modified54),Modified3:Modified54,0

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!