Auto fill blank cells with cell value, if condition is met

Question

Initial layout

I am attempting to auto fill column C with type column B title, from the nth instance where column A = "D", and to automatically populate the value, until the next occurrence of A = "D", then use the next instance where column A = "D".

Desired output

Answer 1

Use XLOOKUP that searches from the bottom up and use dynamic ranges:

=XLOOKUP("D",$A$1:A2,$B$1:B2,"",0,-1)

Or if you want it to spill:

=SCAN("",ROW(A2:A13),LAMBDA(z,y,XLOOKUP("D",$A$1:INDEX(A:A,y),$B$1:INDEX(B:B,y),"",0,-1)))

---

Then there is the simply dragdown formula:

=IF(A2="D",B2,C1)

Answer 2

I know am late here, but here is my attempt using XLOOKUP() & SCAN():

---

=LET(a, "D"&SCAN(0,A2:A18="D",LAMBDA(x,y,IF(y,x+1,x))), XLOOKUP(a,a,B2:B18))

---

Answer 3

You could choose to not use XLOOKUP in this case:

=SCAN("",A2:A18,LAMBDA(a,b,IF(b="D",OFFSET(b,,1),a)))

Or to avoid OFFSET: =LET(a,A2:A18,b,B2:B18,SCAN("",SEQUENCE(ROWS(a)),LAMBDA(x,y,IF(INDEX(a,y)="D",INDEX(b,y),x))))

Or using XLOOKUP and MAP to spill: =MAP(A2:A18,B2:B18,LAMBDA(a,b,XLOOKUP("D",A2:a,B2:b,,,-1)))