Formula to find the value of the closest non-blank cell to the left

[MisterProzilla]

Hi,

I'm stuck on finding a formula that returns the value of the closest / most recent
non-blank cell to the left.

So, for example, in the range A1:E1, I have values in A1, B1 and C1, but D & E are blank; I'd like a formula in F1 that looks to the left and returns the value of C1, the closest non-blank cell.

Any help would be appreciated

 

[kalikj]

Enter the following formula into cell F1: =OFFSET(A1,,COUNTIF(A1:E1,">0")-1,,)

This will give you value of the first non blank cell in the range A1 to E1.

 

[MisterProzilla]

Agh, So close!

The problem with your suggestion is that I don't necessarily always have an unbroken row of filled cells up to the one I'm trying to reference - there may be gaps. So let's say I've got data in A, B, and D, but C is blank - I want the value of D, so I need the column part of the syntax to return a 4, but the COUNTIF part is going to return a 3 as it's counting only 3 non-blank cells.

I also need it to bring back any values, not just numbers, so I've changed it to:
=OFFSET(A1,,COUNTIF(A1:E1,"<>"&"")-1,,)

Offset seems like the right way to go, it's just getting that column number

 

[kalikj]

Ah, I see. Then you don't need offset. The following will find the value of the last non blank cell:

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

This will return the value whether it is numeric or text.

 

[MisterProzilla]

Ah, I see. Then you don't need offset. The following will find the value of the last non blank cell:

=LOOKUP(2,1/(A1:E1<>""),A1:E1)

This will return the value whether it is numeric or text.

That's it, exactly what I was after! Fantastic

Apologies also kalikj, I didn't realise it was you replying to my other similar thread; I've just fed you your own formula

Thank you from the bottom of my shrivelled heart

 

[jonidontcry]

That's fantastic answer. I have the same question today & luckily found this thread. Thank you very much.