Excel 365 formula to retrieve client's newest score

Question

I am using this formula to pull the client's highest score:

=MAX(IF(FILTER(Appointments!G:G, Appointments!C:C=C2)>0,FILTER(Appointments!G:G,
Appointments!C:C=C2)))

What I want is the most recent score. Appointments!G is the score, Appointments!C is the client ID, Appointments!D is the date of the score.

Answer 1

you could use the below trick of match formula.

=MATCH(1,(C2=Appointments!C:C)* ((MAX(FILTER(Appointments!D:D,Appointments!C:C=C2)))=Appointments!D:D),0)

this formula will return the row index for the most recent score.

then you could combine it with the index function to get the value.

=index(Appointments!G:G,MATCH(1,(C2=Appointments!C:C)* ((MAX(FILTER(Appointments!D:D,Appointments!C:C=C2)))=Appointments!D:D),0))

the bold part is to get the most recent date:

(MAX(FILTER(Appointments!D:D,Appointments!C:C=C2)))