Search code examples
excelexcel-formulaautofill

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

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

Solution

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

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

    enter image description here

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

    enter image description here

    Then there is the simply dragdown formula:

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

    enter image description here