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

Column A	Column B	Column C Expected result
10	A	A
	B	A
	C	A
20	D	D
	E	D
	F	D
	G	D
30	H	H
	I	H

<tbody>
</tbody>

 

[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.