Get the next non-blank cell above a cell in Google Sheets

[Michelleaneous]

Hello,

What should be the formula to use in C3 in order to look up for the last non-blank above A3?

In Col D is the desired result

 

[Fluff]

Not sure if this works in sheets, but try

=LOOKUP(2,1/(A$2:A2<>""),A$2:A2)&" - "&B2

 

[Michelleaneous]

It did work, however, it will give me an NA if i drag the formula so I have to adjust every cell

 

[Fluff]

There should be no need to adjust the formula if you have anchored the ranges as I showed.

 

[Michelleaneous]

Thanks for checking on this. Could you please check what I did wrong? It's still showing NA errors for the rows with blank cells in Col A after dragging the formula down

 

[Fluff]

Ok, it appears that lookup works differently in Sheet, try

=INDEX(A$1:A1,MATCH("zzzz",A$1:A1))& " - "&B1

 

[Michelleaneous]

Ok, it appears that lookup works differently in Sheet, try

=INDEX(A$1:A1,MATCH("zzzz",A$1:A1))& " - "&B1

Works like magic! Thanks a million

 

[Fluff]

You're welcome & thanks for the feedback.