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

System

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

LydiaB1029

@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.

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.

Cheers,

Genevieve

LydiaB1029

@Genevieve P. Great, thank you!