How to use Variable Lookup_array with Xmatch formula, =XMATCH(Student_Name,$B$3:B216,0,-1)

Question

I am using and excel to make student exam marks record to see progress, keeping latest three records, last record is perfectly shown using XMATCH but second last or third last having error,

XMATCH consider first LOOKUP_ARRAY reference only but I want all reference should be taken from last one using named range or $B$3:B216 should updated by $B$3:L1, L2, L3……. Value like:

Result Lookup_array Limit Range by existing value

XMATCH(Student_Name,$B$3:B216,0,-1)................$B$3:B216................where L2 is 216 XMATCH(Student_Name,$B$3:B235,0,-1)................$B$3:B235................where L3 is 236 XMATCH(Student_Name,$B$3:B180,0,-1)................$B$3:B180................where L4 is 180 XMATCH(Student_Name,$B$3:B513,0,-1)................$B$3:B513................where L5 is 513 XMATCH(Student_Name,$B$3:B43,0,-1)...................$B$3:B43..................where L6 is 43 XMATCH(Student_Name,$B$3:B296,0,-1)................$B$3:B296................where L7 is 296 XMATCH(Student_Name,$B$3:B415,0,-1)................$B$3:B415................where L8 is 415 XMATCH(Student_Name,$B$3:B319,0,-1)................$B$3:B319................where L9 is 319

Name ranged required for only one formula calculates all column

tired =XMATCH(Student_Name,$B$3:B216,0,-1)

and

=XMATCH(Student_Name,Latest_Marks,0,-1) but not works, // Latest_Marks Named Ranged for “L column / Range limit”

Copy of sheet attached here https://docs.google.com/spreadsheets/d/1yCuMZiYkkhKZWZQIKqDYT97aos7Jb-7M/edit?usp=share_link&ouid=116840440138009717475&rtpof=true&sd=true

Answer 1

Instead of calculating the row of the latest match and excluding that from the range you could switch to using FILTER combined with SORTBY:

=LET(sorted,SORTBY(HSTACK(Test_Student_Name,Test_Marks),D3:D219,-1),
     name,INDEX(sorted,,1),
     mark,INDEX(sorted,,2),
DROP(
     REDUCE(0,Student_Name,
     LAMBDA(x,y,
            VSTACK(x,
                   TRANSPOSE(
                             TAKE(
                                  FILTER(mark,name=y),
                                  3))))),
     1))

First sorted makes sure the data of Test_Student_Name and Test_Mark ate sorted by the Test_Dates (this isn't currently a named range, so I referenced the actual range in the formula. HSTACK(Test_Student_Name,Test_Mark) could be replaced with B3:B219)

I then split them in name and mark for readability and later use in the formula.

The DROP/REDUCE formula behaves like a 2D spill of the BYROW function, so we can spill the transposed filter result of the sorted marks, where the sorted student name equals the current Student_Name of that row. This would result in each grade for that student. Wrapping it in TAKE(FilteredSortedMarks,3) results in the first 3 marks of the sorted data new to old for given student.