How to find the last instance of data in a series of cells?

[stuckagain22]

I have a spreadsheet that consists of a data area which spans about 200 columns and 30 rows.

I want a column to show the last instance of cell value in that row of 200 cells.

i.e.

| 2 | 5 | 4 | 9 | 1 | _ | _ | 3 | _ | _ |

In the above example I want to show that "3" is the last value in the row.

I can't think of good way to do this. Any suggestions?

 

[stuckagain22]

After a fairly lengthy Google search, I think I found a way to do this. (Actually 2 ways, but one uses Lookup and ROW references -- and I always avoid ROW references as I still don't know if Excel has ever fixed its internal error these used to cause).


This seems to be working:
=OFFSET(A1,0,MATCH(MAX(A1:GZ1)+1,A1:GZ1,1)-1)

 

[jtakw]

Hi,

LOOKUP formula, no need for ROW function, And without the volatile OFFSET function.

Use L1 formula if the Values are Numbers,
Use L2 formula if the Values are Text,
Use L3 formula if the Values are Mixed:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	2	5	4	9	1			3				3
2	a1	b2	c3	d4	e5			f6				f6
3	2	b	4	d	1			oo				oo
Sheet613

Cell Formulas
Range	Formula
L1	=LOOKUP(9.99999999999999E+307,A1:J1)
L2	=LOOKUP("zzz",A2:J2)
L3	=LOOKUP(2,1/(A3:J3<>""),A3:J3)

 

[stuckagain22]

LOOKUP formula, no need for ROW function, And without the volatile OFFSET function.

Thanks a lot jtakw.

That is a much simpler Lookup than the ones I found. In my case it is just numbers that will be in the row. The numbers will never be higher than 365. Would I still need to use "9.99999999999999E" or could this be shortened? I'm also not sure what the significance of "+307" is, but is this just part of the "magic" that makes this formula work when combined with the above number?

 

[jtakw]

If you're Absolutely Certain your numbers will Never be larger than 365, you can effectively use 366...or use the alternative below.
That number I used 9.999...E+307, often referred to as the "Big Num", is supposedly the largest number Excel can handle (although there's some debate on this).

=LOOKUP(366,A1:J1)
=LOOKUP(9^9,A1:J1)