You could transfer only your 20 most recent rounds to the second table, then do the ranking and selection of the 10 best there.
To do this, you would need to record the date for each round (and a date and time, should you have had more than one round in a day).
The 20 most recent rounds would then be the ones located, and transferred to column A of Table 2, searching for the 20 'largest' Date & Time values in column A ot Table 1.
Note: In the example, the 'dates' for the two rounds on Feb 11 were entered on Table 1 as Feb 1, and Feb 1, with the D&T value edited to Feb 11, 2019 01:00. Setting a time later than 00:00:00 for the second appearance of that date makes the two date & Time values distinct, and lets both be captured in the 'most recent 20'.
Scores for those 20 rounds are then retrieved using MATCH to provide the position in the list of each of the ten lowest scores, and INDEX to retrieve those scores using the values provided by MATCH.
Formulas used are listed below the image of the three tables (and sample scores) below.
Note that I have hidded rows 5 to 25 of Table 1 to make he tables fit easily into the available space here.
Formulas:
Table 2::A2, and filled down to row 21:
LARGE(Table 1::A,ROW()−1)
This returns the date and time value associated with the 20 most recent rounds recorded in Table 1.
Table 2::B2, and filled down to row 21:
INDEX(Table 1::B,MATCH(A2,Table 1::A,0))
MATCH returns the position of each of the 20 Date and Time values listed in column A of 'this table', and returns this value to INDEX.
Index returns the score from the row of Table 2 indexed by MATCH.
Table 3::A2. and filled down to A11:
IF(ROW()>11,"",SMALL(Table 2::B,ROW()−1))
This returns the ten lowest scores from the 20 most recent rounds, listed on Table 2, ready for the calculations that will extract a handicap value form these scores.
Regards,
Barry
