finding the max date per unique id and return value in certain column

Occasional Contributor



Hope someone can help I have the below.

I want to find the latest date per each unique ID and return the corresponding status.


In this case 

A = inbound

B = complete


6 Replies



Enter above formula as arrayformula with ctrl+shift+enter and unique IDs in E2 and E3 and copy formula down.


Is this the formula you are looking for?

this is great. I forgot to mention my data set is in a table will it still work?


I entered your data as dynamic table and added data. The formula was automatically adapted according to the added rows of the dynamic table and the results seem to be correct.

in your example shouldnt it return

Largest date for A is 12/31 therefore corresponding status is Sale
Largest date for B is 12/30therefore corresponding status is Purchase


Dates of december are of 2020. I forgot to change the format of column B sorry. Please compare attached file.