last non-blank cell, column to left

orsm6

Active Member
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,362
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try:
=INDEX(A:A,MATCH(LOOKUP(2,1/(B:B<>""),A:A),A:A,0)+1)
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,988
Messages
5,575,390
Members
412,659
Latest member
oliverreyes
Top