Pull data from last cell in a row

[bmckenna]

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

 

[pavin]

Assuming you are looking for column A,

Try this formula:

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

 

[pavin]

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]

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]

Try this:

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

 

[drsarao]

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]

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]

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)))))