# last non-blank cell, column to left

#### orsm6

##### Active Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### JoeMo

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

#### alansidman

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

VBA Code:
``````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

##### Active Member
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

##### Active Member

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

VBA Code:
``````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

##### Well-known Member
ok! Have a good day.

#### JoeMo

##### MrExcel MVP
Thanks Joe .... i was close on my attempt haha. worked perfectly.
You are welcome - thanks for the reply.

Replies
9
Views
165
Replies
17
Views
295
Replies
5
Views
75
Replies
3
Views
37
Replies
1
Views
36