Google sheets - what function can I use to expand MAXIFS function to an entire column?

Question

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.

=MAXIFS(ContactLog!B:B, ContactLog!A:A, A:A)

=MAX(FILTER(ContactLog!B4:B, ContactLog!A4:A=VLOOKUP(A2, ContactLog!A4:B, 1, FALSE)))

=MAX(QUERY(ContactLog!A4:B, ""SELECT B WHERE A = '""&VLOOKUP(A2, ContactLog!A4:B, 1, FALSE)&""'"", 0))

=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).

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

[I have a similar issue for the nearby columns also, "most recent interaction method", and "reminders & goals". The formulas there are: =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))))

Answer 2

Thanks Martin.

Thanks as well to anonymous who went ahead and edited the sample sheet for me, using XLOOKUP. I'll post it here for those who need: =ArrayFormula(XLOOKUP(A2:A,ContactLog!A4:A,ContactLog!C4:C,,,-1))

I had to tweak it because it wasn't returning the most recent result. So i just added 2 SORTs and it worked. Something like this. =ArrayFormula(XLOOKUP(A2:A,SORT(ContactLog!A4:A,ContactLog!B4:B,false),SORT(ContactLog!C4:C,ContactLog!B4:B,false),,,-1))

Later I just did it with vlookup: =ArrayFormula(VLOOKUP(A2:A,sort(ContactLog!A4:C,ContactLog!B4:B,false),3,FALSE))

But Martin's way is best, I applied it to other more unique formulas and it works great!

Thanks!