I have two lists, one with vehicle IDs (column A) and dates they were used (Column B), another one with IDs (Column E) and all dates maintenance was performed on them (Column F). In Column C I would like a formula to return for each usage, the last time date when that specific vehicle was serviced.
I'm really bad at Gsheet so all I managed to do until now is to get the latest day a vehicle was serviced with this formula =MAX(FILTER(E$2:F, E$2:E=A2)). I'd need some way to add a condition where D<B. I tried a MAXIFS formula but I just don't have the knowledge to get the syntax right. Can anyone help?
Here's the expected result: enter image description here
Try this single array-style formula (don't drag it down) in Cell_C2:
=map(A2:A,B2:B,lambda(a,b,if(or(a="",b=""),,max(filter(F:F,E:E=a,F:F<b)))))