INDEX MATCH with MAX

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([email protected], {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

Tags:

Best Answer

Answers

  • Mike TV
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this:


    =MAX(COLLECT({Date Range}, {Employee ID Range}, @cell = [email protected]))

    thinkspi.com

  • Mike TV
    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
    Cheryl Collins ✭✭✭✭✭✭

    Hi @Paul Newcome

    Your formula has worked perfectly. Thank you 👍