Index/Match/Max Function Issues

dc13

@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

Summer

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

=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