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

Occasional Contributor

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

Hello,

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

 ID Date Status A 1-Jan complete A 2-Jan empty A 3-Jan inbound B 1-Jan empty B 2-Jan complete
6 Replies

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

=INDEX(\$B\$2:\$C\$6,MATCH(E2&LARGE(IF(\$A\$2:\$A\$6=E2,\$B\$2:\$B\$6),1),\$A\$2:\$A\$6&\$B\$2:\$B\$6,0),2)

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?

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

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

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

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.

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

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

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

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

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

Awesome! Danke schön