Grabbing last data

[vlad n]

Hello,
I am looking for a function tool to grab the last value of a column even if there are empty cells or not as well as the second to last cell. I have tried INDEX, it did it except the empty cells it did not like. I then used LOOKUP but it then could not give me second to last cell by trying the "-1" at the end.

[JBeaucaire]

Assuming a range of only F1:F16, this array formula can be used:

=INDEX($F$1:$F$16, LARGE(IF($F$1:$F$16>0,ROW($F$1:$F$16), 0), 1))
=INDEX($F$1:$F$16, LARGE(IF($F$1:$F$16>0,ROW($F$1:$F$16), 0), 2))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Tony Valko]

What type of data is it? Is it text? Numbers? Could be both? Something else?

[vlad n]

Thanks JBeaucaire,
Had to make some changes instead of the 1 and 2 at the end, 2 and 3 I had to use. However, after testing it, if there are empty cells close to the end, it does not like it and outputs empty values even if the empty cell is not second to last or the last one.

Tony, the data is just numerical ones. No text.

[Tony Valko]

Tony, the data is just numerical ones. No text.

Try these...

Data Range

	A	B	C	D
1	36		Last	2nd Last
2	67		69	74
3	24
4	9
5
6	98
7	90
8	24
9	2
10	33
11	74
12	------	------	------	------
13
14
15	69

This formula entered in C2:

=LOOKUP(1E100,A1:A15)

This array formula** entered in D2:

=INDEX(A:A,LARGE(IF(ISNUMBER(A1:A15),ROW(A1:A15)),2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[vlad n]

Tony, that is exactly what I needed. It works in all cases I threw at it. Thank you for your help.

[Tony Valko]

You're welcome. Thanks for the feedback!