Forum Discussion
Solved
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 |
Dates of december are of 2020. I forgot to change the format of column B sorry. Please compare attached file.
6 Replies
=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?
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.
