How to use the formula RANK() on google sheet for each new date?

Question

I do not know how to dynamically change the RANK formula based on the date.

For each day (or each new date) I have to rewrite the formula in order to match the range of the D-Day.

Date    Name    Points  Ranking Reward
27 Jul  Andy    3          3    6
27 Jul  Mike    14         2    8
27 Jul  Lucy    78         1    10
26 Jul  Andy    45         2    8
26 Jul  Mike    65         1    10
26 Jul  Lucy    12         3    6
25 Jul  Andy    123        3    6
25 Jul  Mike    23423      2    8
25 Jul  Lucy    466566     1    10

See the google sheet here : example

The datas are added dynamically each day so I would like it to be automatic. I am open to try some script as well if needed. Thank you :-)

P.S : I also found this question : similar problem ?

But I did not understand how the formula response works and I am not sure I can apply it to my problem here.

Answer 1

use in D1:

=ARRAYFORMULA({"Ranking"; IFERROR(1/(1/VLOOKUP(ROW(A2:A), {
 SORT(ROW(A2:A), A2:A, 0, C2:C, 0), 
 COUNTIFS(SORT(A2:A, 1, 0, C2:C, 0), SORT(A2:A, 1, 0, C2:C, 0), 
 ROW(A2:A), "<="&ROW(A2:A))}, 2, 0)))})