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)
+===========================================================+
``````

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)
+===========================================================+
``````
Who is Participating?

Commented:
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

regards, barry
0

Commented:
Hi,

try

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

Kris
0

Commented:
Try this formula in A1 copied down

regards, barry
0

VBA 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

Commented:
Hi

Ignore the earlier post. Try

Kris
0

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

But, it continues to return #N/A
0

Commented:
Hi,

Kris
0

Commented:
For text or numbers....

regards, barry
0

Commented:
My version goes in A1, clearly for the range B8:RJ8 change to this in A8

barry
0

Author 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.