Return closest non blank value

[BumbleBee31]

HI Everyone,

Need help with formula which can return the closest non blank value. Providing sample data table below. Column C shows the results i am expecting. Thanks [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C Expected result[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]D[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]F[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]G[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]H[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I[/TD]
[TD]H[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Based on your sample you can do it like this:

=IF(A2<>"",B2,C1)

 

[footoo]

.

 

[BumbleBee31]

Hi, thanks but it won't work when the value in column B changed to D

 

[steve the fish]

It will if the sample is the same as your sheet.

 

[BumbleBee31]

Yes I checked again and it works but i need to manually input the value in C1 and start the formula from C2

 

[steve the fish]

Not if your sample data is representative you dont. If what you are saying is true then cell A2 is blank.