Pull data from last cell in a row

bmckenna

New Member
Joined
Oct 15, 2009
Messages
33
I need an excel formula, preferably no VBA, that finds the last value in a row range.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

pavin

Active Member
Joined
Jan 20, 2009
Messages
308
Assuming you are looking for column A,

Try this formula:

=OFFSET(A1,COUNT(A:A)-1,0)
 

pavin

Active Member
Joined
Jan 20, 2009
Messages
308
Assuming you are looking for column A,

Try this formula:

=OFFSET(A1,COUNT(A:A)-1,0)

Make sure your data starts from Cell A1
 

bmckenna

New Member
Joined
Oct 15, 2009
Messages
33
Well, this is what I did: =OFFSET(B28,COUNT(28:28)-1,0)

I want the last value in row 28, that starts at cell B28 and goes to the right. I just get #REF back when I do.
 

pavin

Active Member
Joined
Jan 20, 2009
Messages
308

ADVERTISEMENT

Try this:

=OFFSET(B28,0,COUNTA(28:28)-2)
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Try:
=INDEX(2:2,,COUNTA(2:2))
This will work correctly only if none of the cells in the range are blank.
If that is not so, a longer formula to find the last non blank cell is required
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I need an excel formula, preferably no VBA, that finds the last value in a row range.

If the row range is text:

=LOOKUP(REPT("z",255),A2:Q2)

=LOOKUP(REPT("z",255),2:2)

If the row range is numeric:

=LOOKUP(9.99999999999999E+307,A2:Q2)

=LOOKUP(9.99999999999999E+307,2:2)
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
If there ARE blank cells in the range then use one of the following:
1. Range has NUMBERS only
=INDEX(2:2,MATCH(9.999999E+306,2:2))

2. Range has TEXT only
=INDEX(2:2,,MATCH("*",2:2,-1))

3. MIXED range with both numbers and text
=INDEX(2:2,,IF(ISERROR(MATCH(9.999999E+306,2:2)),MATCH("*",2:2,-1), IF(ISERROR(MATCH("*",2:2,-1)),MATCH(9.999999E+306,2:2), MAX(MATCH(9.999999E+306,2:2),MATCH("*",2:2,-1)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,567
Messages
5,572,962
Members
412,491
Latest member
tweetytoon
Top