# Pull data from last cell in a row

#### bmckenna

##### New Member
I need an excel formula, preferably no VBA, that finds the last value in a row range.

### 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
Assuming you are looking for column A,

Try this formula:

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

#### pavin

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

Try this:

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

#### drsarao

##### Well-known Member
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

##### MrExcel MVP
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
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)))))

Replies
6
Views
165
Replies
9
Views
150
Replies
8
Views
66
Replies
3
Views
179
Replies
3
Views
60

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