Excel 2008 - Find last cell with data in row

I've searched for an answer, but it seems that I can only find VBA-based solutions, which appears to no longer be an option in Excel 2008.  

I'm trying to return the name of the last cell in a row that contains data.  

So, it looks like this:

+===========================================================+
                 A      /  B  /  C  /  D  /  E  (columns)
+===========================================================+
1         || [Formula] /  3  /  4  /  5  / [blank]
2         || [Formula] /  7  /  8  / [blank] / [blank]
3         || [Formula] /  9  / 10 / 11 / 12
(rows)
+===========================================================+

Open in new window


 And, the end result would look like this:

+===========================================================+
               A       /  B  /  C  /  D  /  E  (columns)
+===========================================================+
1         || D1 /  3  /  4  /  5  / 
2         || C2 /  7  /  8  /     / 
3         || E3 /  9  / 10 / 11 / 12
(rows)
+===========================================================+

Open in new window

n00b0101Asked:
Who is Participating?
 
barry houdiniCommented:
Sorry, scratch that last one, for that version to work the range should start at column A. Perhaps an amalgamation of the formula suggested by Kris....and my one, i.e. in A8

=CELL("address",INDEX(B8:IV8,MATCH(2,INDEX(1/(B8:IV8<>""),0))))

regards, barry
0
 
krishnakrkcCommented:
Hi,

try

=index(b$1:e$1,match(9.99999e+307,b2:e2))

Kris
0
 
barry houdiniCommented:
Try this formula in A1 copied down

=ADDRESS(ROW(),MATCH(9.99E+307,1:1),4)

regards, barry
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
NorieVBA ExpertCommented:
Are you using a Mac?

There are formulas to get the last value in a row, for example

=LOOKUP(9.99E+99,2:2)

Put this in A1 in and be copy down.

I'm sure getting the address, eg D1, etc but that's a but beyond me.
0
 
krishnakrkcCommented:
Hi

Ignore the earlier post. Try

=CELL("address",INDEX(C1:K1,MATCH(9.9999E+307,B1:K1)))

Kris
0
 
n00b0101Author Commented:
I've tried all of these, but it returns #N/A.  In some cases, the cell will contain text... I tried altering the MATCH statement to:

MAX(MATCH(9.9999E+307,B8:RJ8),MATCH(REPT("z",255),B8:RJ8))

Open in new window


But, it continues to return #N/A
0
 
krishnakrkcCommented:
Hi,

=CELL("address",INDEX(C1:K1,MAX(IFERROR(MATCH(9.9999E+307,B1:K1),0),IFERROR(MATCH("zzzzzzzzz",B1:K1),0))))

Kris
0
 
barry houdiniCommented:
For text or numbers....

=ADDRESS(ROW(),MATCH(2,INDEX(1/(1:1<>""),0)),4)

regards, barry
0
 
barry houdiniCommented:
My version goes in A1, clearly for the range B8:RJ8 change to this in A8

=ADDRESS(ROW(),MATCH(2,INDEX(1/(B8:RJ8<>""),0)),4)

barry
0
 
n00b0101Author Commented:
Thanks, this one works.  I'm splitting the points (hopefully, fairly)...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.