MAX(COLLECT({Range 1}, {Employee Range}, @cell = Employee@row))

This discussion was created from comments split from: INDEX MATCH with MAX.


  • LydiaB1029
    edited 04/19/24

    @Paul Newcome Could you update this formula for two ranges? For example If we needed to know the latest date by name and by review type. For example, if there were another column review type with two values "performance" or "development", and I need to find the latest date for Paul's performance review.

    Thanks in advance!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @LydiaB1029

    With a COLLECT function, all you need to do is add the next {column range} and "criteria" with a comma in between to filter.

    =MAX(COLLECT({Range to Max}, {Column 1}, [First Criteria]@row, {Column 2}, [Second Criteria]@row))

    Does that make sense? So in your case something like:

    =MAX(COLLECT({Date Column}, {Name Column}, Name@row, {Review Column}, Review@row))

    If this hasn't helped, it would be useful to see a screen capture of your source sheet, but please block out sensitive data.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!