I have a tab where there's multiple values for the same ID:
| Date | ID | Column C |
|---|---|---|
| 2021/11/23 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/11/24 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/11/25 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/11/26 9:01 PM | 1427691673 | 2021-02-10 |
| 2021/11/27 9:01 PM | 1427691673 | 2021-02-10 |
| 2021/11/28 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/11/29 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/11/30 9:02 PM | 1427691673 | 2021-02-10 |
| 2021/12/1 9:02 PM | 1427691673 | 2021-02-10 |
I would like to get the latest value for column C based on the latest date in column A. I have done something similar with Index Match but I don't know how to filter it to get the MAX date.
CodePudding user response：
Delete everything from Col C (including the header) and place the following formula in C1:
=ArrayFormula({"Header Text";IF(B2:B="",,VLOOKUP(B2:B,SORT({B2:B,A2:A},2,0),2,FALSE))})
You can change the "Header Text" label as desired within the formula itself.
Basically, this just looks up each ID in a virtual array sorted in reverse order by date. So the first match found will be the last date found.