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.
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.
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 <[email protected]>,
geoff1234 <[email protected]>
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks