Set row maximum for FILTER formula

Question

I have a dataset where I will be using the FILTER formula to extract specific values relative to an individual. For each individual, I only want to be extracting the last 2 scores. The data is in descending chronological order so effectively I just need the first 2 entries that exist.

Year	Name	Score
2022	A	50
2022	B	40
2022	C	60
2021	A	10
2021	B	5
2020	A	90
2020	B	76
2019	A	45
2019	C	12
2018	A	14
2017	A	13

Using a dataset similar to the one attached, if I wasn't interested in only the last 2 scores, I would use something like

=FILTER(A:C,B:B=B1) B1 for A, B2 for B etc.

But doing that would give me 6 rows for A, 3 for B and 2 for C. To standardize this, I only want to consider, at max, 2 results per individual. How do I change the FILTER formula to achieve this?

Answer 1

Don't use entire column references: FILTER's include parameter processes all cells passed to it, so you're effectively asking it to compute more than a million calculations, even if you only have a few hundred rows' worth of data.

You could use TAKE if your version of Excel has that function:

=TAKE(FILTER(A1:C12,B1:B12=B2),2)

or else INDEX with SEQUENCE:

=INDEX(FILTER(A1:C12,B1:B12=B2),SEQUENCE(2),SEQUENCE(,3))

If you're not sure how to create a dynamic reference to the last-used row, define LRow within Name Manager as:

=MATCH("Ω",$B:$B)

after which the first of the above becomes:

=TAKE(FILTER(A1:INDEX(C:C,LRow),B1:INDEX(B:B,LRow)=B2),2)