last non-blank cell, column to left

[orsm6]

Hi everyone. could someone help me please with a formula? trying to get last non-blank cell in column B then look left to column A and return value of next row down.

so if B20 was last non-blank cell, would like to return value in A21.

TIA.

 

[JoeMo]

Try:
=INDEX(A:A,MATCH(LOOKUP(2,1/(B:B<>""),A:A),A:A,0)+1)

 

[alansidman]

Since you didn't indicate where the result should go, I have put it in a message box.

Option Explicit

Sub Orsm()
    Dim lr As Long, ans
    lr = Range("B" & Rows.Count).End(xlUp).Row
    ans = Range("B" & lr).Offset(1, -1)
    MsgBox (ans)

End Sub

 

[orsm6]

Try:
=INDEX(A:A,MATCH(LOOKUP(2,1/(B:B<>""),A:A),A:A,0)+1)

Thanks Joe .... i was close on my attempt haha. worked perfectly.

 

[orsm6]

Since you didn't indicate where the result should go, I have put it in a message box.

Option Explicit

Sub Orsm()
    Dim lr As Long, ans
    lr = Range("B" & Rows.Count).End(xlUp).Row
    ans = Range("B" & lr).Offset(1, -1)
    MsgBox (ans)

End Sub

Hi Alan - thanks for the reply, needed a formula not a VBA.

cheers.

 

[alansidman]

ok! Have a good day.

 

[JoeMo]

Thanks Joe .... i was close on my attempt haha. worked perfectly.

You are welcome - thanks for the reply.