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?

CodePudding user response：

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