First and last cell in a column

[phoebian]

Hi all,

I am looking for 2 similar formulas which provide the following:

Formula 1: The data from the first none empty cell in a column
Formula 2: The data from the last none empty cell in a column

I found the following which provides the first: =INDEX(A2:A7,MATCH(TRUE,F:F<>"",0))
But I am unable to turn it around to show the last value.

The spreadsheet is large so needs to be efficient in its calculations.
The first few cells may be empty.
I was hoping both formulas would be of a similar function (IE, both using =INDEX, or =VLOOKUP).

So in the example table below, formula 1 should result in 1 or 2 (depending on which column we run it on), and formula 2 should result in 5 or 6 (depending on which column we run it on)

Odd Numbers	Even numbers
1
	2
3
	4
5
	6

THANKS!

 

[jtakw]

Hi,

For the last value in column, assuming you mean Numbers and not Text:

Book3.xlsx
	A	B	C	D	E
1	Odd Numbers	Even numbers		For A	For B
2	1			5	6
3		2		5	6
4	3
5		4
6	5
7		6
Sheet778

Cell Formulas
Range	Formula
D2:E2	D2	=LOOKUP(1,1/A2:A10,A2:A10)
D3:E3	D3	=LOOKUP(9.99999999999999E+307,A2:A10)

 

[phoebian]

Thanks; yes I think this will only be numeric values so it looks good.

Is there a similar way to use =LOOKUP(1,1/A2:A10,A2:A10) for the first column with a value? I have OCD.

 

[jtakw]

You're welcome, thanks for the feedback.

I'm afraid the INDEX/MATCH formula you're using is the best way to get the First value in column (although I believe your F:F reference should be A2:A7)...

We could use a VLOOKUP, for the 1st value, but Only if it's Text.