Let's say I have a table with three columns (order is important and can't be changed), rows are sorted by A:
A B C date ID last_date 10/05/2015 29A06E95 10/08/2015 E5A884C4 10/12/2015 D24F4975 10/12/2015 D24F4976 10/21/2015 29A06E95
For any row, if the ID also shows up in any row above the current, I want to populate column C with the most recent date (or, since the rows are sorted by A, the date value from the previous row with the same ID). For example, here the result would look like this:
A B C date ID prev_date 10/05/2015 29A06E95 <blank> 10/08/2015 E5A884C4 <blank> 10/12/2015 29A06E95 10/05/2015 10/12/2015 D24F4976 <blank> 10/21/2015 29A06E95 10/12/2015
How do I do this (I haven't been able to use either MATCH or VLOOKUP since the former finds the first match and the latter wants the search value in the first column)?
Also, I've seen solutions with array formulas, but these don't seem to work within a data table context.