Hi Dudes,

Can anyone help with this problem:

I've got a list of data as such

34
54
23
34
blank
77
45
67
45
blank


To get the last non-blank cell value I'm using =LOOKUP(1E+40,A1:A10).

Anyone know a formula of how to look up the 2nd last value.


--
geoff1234
------------------------------------------------------------------------
geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051
View this thread: http://www.excelforum.com/showthread...hreadid=559340

First, define the following...

Insert Name Define

Name: BigNum

Refers to: =9.99999999999999E+307

Click Ok

Then, try the following formula...

=LOOKUP(BigNum,A2:INDEX(A2:A10,MATCH(BigNum,A2:A10 )-1))

Hope this helps!

In article ,
geoff1234
wrote:

Hi Dudes,

Can anyone help with this problem:

I've got a list of data as such

34
54
23
34
blank
77
45
67
45
blank


To get the last non-blank cell value I'm using =LOOKUP(1E+40,A1:A10).

Anyone know a formula of how to look up the 2nd last value.

=INDEX(A1:A10,LARGE(IF(A1:A10<"",ROW(A1:A10)),2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"geoff1234" wrote
in message ...

Hi Dudes,

Can anyone help with this problem:

I've got a list of data as such

34
54
23
34
blank
77
45
67
45
blank


To get the last non-blank cell value I'm using =LOOKUP(1E+40,A1:A10).

Anyone know a formula of how to look up the 2nd last value.


--
geoff1234
------------------------------------------------------------------------
geoff1234's Profile:
http://www.excelforum.com/member.php...o&userid=36051
View this thread: http://www.excelforum.com/showthread...hreadid=559340

thanks a lot for that you two, got it working now

ta


--
geoff1234
------------------------------------------------------------------------
geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051
View this thread: http://www.excelforum.com/showthread...hreadid=559340