Sep 07 2022 04:56 PM - edited Sep 07 2022 07:49 PM
Hello!
I am finding very challenging on getting a correct formula on below.
Can you please help me in creating a formula to repeat the science score for each student name until there is a new science score.
I have attached the the example file and highly appreciate if someone can give me some insights on defining the correct formula.
Sep 07 2022 07:16 PM
Solution@rasikedesilva Try below formula-
=XLOOKUP(1,FILTER($E$2:$E2,$D$2:$D2=D2),FILTER($E$2:$E2,$D$2:$D2=D2),"",1,-1)
For dynamic spill array try below formula. See the attached file.
=BYROW(D2:D13,LAMBDA(x,LET(a,FILTER(E2:E13,(D2:D13=x)*(ROW(E2:E13)<=ROW(x))),XLOOKUP(1,a,a,"",1,-1))))
Sep 07 2022 07:48 PM
Thank you so much. This is working wine :) @Harun24HR
Sep 07 2022 07:16 PM
Solution@rasikedesilva Try below formula-
=XLOOKUP(1,FILTER($E$2:$E2,$D$2:$D2=D2),FILTER($E$2:$E2,$D$2:$D2=D2),"",1,-1)
For dynamic spill array try below formula. See the attached file.
=BYROW(D2:D13,LAMBDA(x,LET(a,FILTER(E2:E13,(D2:D13=x)*(ROW(E2:E13)<=ROW(x))),XLOOKUP(1,a,a,"",1,-1))))