INDEX MATCH with MAX

Cheryl Collins

Hi

Scenario: Reviews are required for people across the firm and some may have more than one review in the year. I need to pull through the date of the last review. My current formula is:

=IFERROR(INDEX({01. File Review Range 2}, MATCH(EEID@row, {01. File Review Range 5}, 0)), "")

This index's the last review date and matches the Employee ID in one sheet, to the Employee ID in my File Review sheet.

Issue: When people have more than one review in the year, the latest date is not shown - only the first

Question: Can I add MAX into the nested index match formula to identify the latest date for that person or, do I need a helper column. If I need a helper column what formula would I use to get to my end goal?

TIA

Cheryl

Paul Newcome

Try something like this:

=MAX(COLLECT({Date Range}, {Employee ID Range}, @cell = EEID@row))

Mike TV

@Cheryl Collins

This might help you get to where you need to be?

Example sheet:

Formula which is in row 1 of Date Last Review column:

=MAX(COLLECT([Review Date]:[Review Date], Name:Name, ="Paul"))

MAX with COLLECT can help you look at a range and return the highest date using Collect to tell it the criteria and range to base the criteria on.

Cheryl Collins

Hi @Matt C.

Unfortunately, this won't work for me as I won't know what the name will be in advance. If I were to use this formula it would mean the person completing the review would need to manually add or change the formula each time (which just won't happen in reality) and hundreds of reviews are done each year so, this is why I use the index match to enable a column formula and flexibility.

Any other ideas?

TIA

Cheryl

Paul Newcome

Try something like this:

=MAX(COLLECT({Date Range}, {Employee ID Range}, @cell = EEID@row))

Mike TV

@Cheryl Collins

Of course they wouldn't have to edit the formula each time. I was showing proof of concept and figured you would know how to change it up as Paul suggested. It sounded like you would have enough knowledge of how to get it mapped correctly due to your starting formula.

Cheryl Collins

Hi @Paul Newcome

Your formula has worked perfectly. Thank you 👍