Forum Discussion

rasikedesilva's avatar
rasikedesilva
Copper Contributor
Sep 07, 2022
Solved

SOLVED:Need help to find a formula to repeat a previous value until there there is new value

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. 

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Sep 08, 2022

    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))))

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 08, 2022

    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))))

     

Resources