Why is Arrayformula returning only the first row

Question

Update: sample sheet provided here: https://docs.google.com/spreadsheets/d/1BapXdaVOUL634SstNJXqYNocsD_EvvtlbJ77vlElmZs/edit?usp=drivesdk. Any help will be appreciated!

Hi fellow nerds.

I'm trying to make the current column (most recent interaction date with client) display the max values (most recent dates) from ContactLog!b:b (dates of all recorded interactions), when the client name in ContactLog!A:A matches to the client name in current row column A.

After many days of trying, I've found several formulas to successfully achieve this result for the current cell only.

1. =MAXIFS(ContactLog!B:B, ContactLog!A:A, A:A)
2. =MAX(FILTER(ContactLog!B4:B, ContactLog!A4:A=VLOOKUP(A2, ContactLog!A4:B, 1, FALSE)))
3. =MAX(QUERY(ContactLog!A4:B, ""SELECT B WHERE A = '""&VLOOKUP(A2, ContactLog!A4:B, 1, FALSE)&""'"", 0))
4. =IF(COUNTIF(ContactLog!A:A, A2),MAX(FILTER(ContactLog!B:B, ContactLog!A:A = A2)),"")

But none of these seem to work with arrayformula, to spread to the entire column. I'd like this result to apply automatically to the entire column (wherever column A is not blank).

It's displaying the correct max value for the first cell (in which the formula is written), and I could drag the formula down, but not spreading automatically as an array.

I've tried using =match with =filter, but that keeps running into mismatched range row sizes. (I've previously solved that by using filter within a filter, but can't figure that out here).

[I have a similar issue for the nearby columns also, "most recent interaction method", and "reminders & goals". The formula there is: =INDEX(ContactLog!C:C, MATCH(MAX(IF(ContactLog!A:A=A2, IF(ContactLog!B:B=MAX(IF(ContactLog!A:A=A2, ContactLog!B:B)), ROW(ContactLog!B:B)))), ROW(ContactLog!B:B), 0))

And

=IFERROR(CONCATENATE(JOIN(" • ",FILTER(ContactLog!D:D,ContactLog!A:A=A2, ContactLog!D:D<>"")),IF(INDEX(ContactLog!D:D,MAX(IF(ContactLog!A:A=A2,ROW(ContactLog!D:D))))="","","")),"")

They both work great, but I can't get them to work with arrayformula...]

What am I missing?

Answer 1

You can do something like this with BYROW, that allows you to expand your formula through the column and be calculated "row by row". Using your first option:

=BYROW(A:A, LAMBDA (each,IF(each="","",MAXIFS(ContactLog!B:B, ContactLog!A:A, each))))