How can I filter by comparing a column to its own row items

Question

I have a bunch of data being pushed in a google sheet row by row. Each row has a reference number (COL A) and a date (COL D). For every reference number, I want to find the latest updated row. It's necessary that this is done by arrayformula.

So far I have a combination of arrayformula and filter. However it doesn't work as expected as one of the filter arguments is A2:A=A2:A. Obviously this is always true. What I want it to do is compare A2:A=A2 for row 2, then compare A2:A=A3 for row 3 etc. Without dragging down. Since the data is growing automatically

=arrayformula(if(len(A2:A),max(filter({D2:D},A2:A=A2:A)),))

https://docs.google.com/spreadsheets/d/16pbGiisFcsrHfrFKowzzkpw03Lw79yACjut96xxyWW4/edit?usp=sharing

I understand your problem and could be easily solvable by a QUERY FUNCTION and dragging down. But why is necessary to do this with ARRAYFORMULA? If you want the QUERY solution let me know and I will post it. — Raserhin
I'm using Zapier to push new data to the sheet based on some trigger events in another app. The data is thus constantly growing. Therefore dragging down is not an option — Wouter Antheunisse
@player0 I want to use that column to indicate if the row is the latest updated row for that specific reference number. The data is not necessarily being pushed in chronological order by Zapier — Wouter Antheunisse

player0 player0 · Accepted Answer · 2020-01-28T16:06:30

try in E1:

={"Last Update"; 
 ARRAYFORMULA(IFNA(VLOOKUP(A2:A; SORT({A2:A\ D2:D}; 2; 0); 2; 0)))}

How can I filter by comparing a column to its own row items

2 Answers