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.

Using a dataset similar to the one attached, if I wasn't interested in the 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?

Thanks!

CodePudding user response：

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)

CodePudding user response：

Using a trick I learned from JvdV here