I think this is complicated but here goes:

I have a spreadsheet that looks like this:

col1 Col2 Col3

bunch of junk
more junk
NUMBER #1234

firstname lastname


This repeats over and over. What I would like to do is populate col3, when
col2 is not null, with the value of the closest previous row that has NUMBER
#9999. Does anyone know a formula or code to do this?

Thanks in advance.