SOLVED

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

Copper Contributor

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

 

IDDateStatus
A1-Jancomplete
A2-Janempty
A3-Janinbound
B1-Janempty
B2-Jancomplete
6 Replies

@cheo_fpc 

=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?

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

@cheo_fpc 

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
best response confirmed by allyreckerman (Microsoft)
Solution

@cheo_fpc 

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

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@cheo_fpc 

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

View solution in original post