Finding the bottom non-blank cell in a range

Forgive me for these trivial questions, but I cant find the answer using the documentation.

I want a formula to use the bottom non-blank cell in a range. How can this be achieved?

try this?

=LOOKUP(2,1/(range<>""),range)

See http://www.xldynamic.com/source/xld.LastValue.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fenneth" <[email protected]> wrote in
message news:[email protected]...
>
> Forgive me for these trivial questions, but I cant find the answer using
> the documentation.
>
> I want a formula to use the bottom non-blank cell in a range. How can
> this be achieved?
>
>
> --
> Fenneth
> ------------------------------------------------------------------------
> Fenneth's Profile:
http://www.excelforum.com/member.php...o&userid=33655
> View this thread: http://www.excelforum.com/showthread...hreadid=534500
>

Hi,

Ias there a similar formula to find the first non blank cell in a range?


Igbert


"starguy" wrote:

>
> try this?
>
> =LOOKUP(2,1/(range<>""),range)
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=534500
>
>

Hi!

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0))

Biff

"igbert" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Ias there a similar formula to find the first non blank cell in a range?
>
>
> Igbert
>
>
> "starguy" wrote:
>
>>
>> try this?
>>
>> =LOOKUP(2,1/(range<>""),range)
>>
>>
>> --
>> starguy
>> ------------------------------------------------------------------------
>> starguy's Profile:
>> http://www.excelforum.com/member.php...o&userid=32434
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=534500
>>
>>

Thanks for the great formula. It works for both text and numbers.

I am new with Index and Match functions. Please kindly explain the logic of
this formula.

Many thanks.


Igbert


"Biff" wrote:

> Hi!
>
> Try this entered as an array using the key combination of CTRL,SHIFT,ENTER:
>
> =INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0))
>
> Biff
>
> "igbert" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Ias there a similar formula to find the first non blank cell in a range?
> >
> >
> > Igbert
> >
> >
> > "starguy" wrote:
> >
> >>
> >> try this?
> >>
> >> =LOOKUP(2,1/(range<>""),range)
> >>
> >>
> >> --
> >> starguy
> >> ------------------------------------------------------------------------
> >> starguy's Profile:
> >> http://www.excelforum.com/member.php...o&userid=32434
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=534500
> >>
> >>
>
>
>

>Please kindly explain the logic of this formula.

Sure!

I'll use a smaller range to demonstrate.

Assume the range of cells is A1:A5:

A1 = (empty)
A2 = (empty)
A3 = (empty)
A4 = XX
A5 = YY

=INDEX(A1:A5,MATCH(TRUE,A1:A5<>"",0))

The Index function holds an array of values. In this case those values are
from the range A1:A5.

Each of these values is in a relative position within the array.

A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
A5 = position 5

Using the formula, we want to find the first non-empty cell in that array so
we can use the Match function to tell the Index function which value to
return.

MATCH(TRUE,A1:A5<>"",0)

This expression will return an array of TRUEs and FALSEs:

A1:A5<>""

A1 <>"" = FALSE
A2 <>"" = FALSE
A3 <>"" = FALSE
A4 <>"" = TRUE
A5 <>"" = TRUE

This is what it looks like inside the Match function:

MATCH(TRUE,{FALSE;FALSE;FALSE;TRUE;TRUE},0)

MATCH returns the number of the relative position of the first instance of
the lookup_value.

The lookup_value is TRUE and has been found in the 4th position within the
array:

{FALSE;FALSE;FALSE;TRUE;TRUE}

So, now the formula looks like this:

=INDEX(A1:A5,4)

This returns the value from the 4th position of the indexed array:

A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
A5 = position 5

So, the formula returns the value from A4:

A1 = (empty)
A2 = (empty)
A3 = (empty)
A4 = XX
A5 = YY

=INDEX(A1:A5,MATCH(TRUE,A1:A5<>"",0)) = XX

Biff

"igbert" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the great formula. It works for both text and numbers.
>
> I am new with Index and Match functions. Please kindly explain the logic
> of
> this formula.
>
> Many thanks.
>
>
> Igbert
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this entered as an array using the key combination of
>> CTRL,SHIFT,ENTER:
>>
>> =INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0))
>>
>> Biff
>>
>> "igbert" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > Ias there a similar formula to find the first non blank cell in a
>> > range?
>> >
>> >
>> > Igbert
>> >
>> >
>> > "starguy" wrote:
>> >
>> >>
>> >> try this?
>> >>
>> >> =LOOKUP(2,1/(range<>""),range)
>> >>
>> >>
>> >> --
>> >> starguy
>> >> ------------------------------------------------------------------------
>> >> starguy's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=32434
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=534500
>> >>
>> >>
>>
>>
>>

Hi Bob,

Many thanks for the thorough explantion. It is very clear.


Igbert


"Bob Phillips" wrote:

> See http://www.xldynamic.com/source/xld.LastValue.html
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Fenneth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Forgive me for these trivial questions, but I cant find the answer using
> > the documentation.
> >
> > I want a formula to use the bottom non-blank cell in a range. How can
> > this be achieved?
> >
> >
> > --
> > Fenneth
> > ------------------------------------------------------------------------
> > Fenneth's Profile:
> http://www.excelforum.com/member.php...o&userid=33655
> > View this thread: http://www.excelforum.com/showthread...hreadid=534500
> >
>
>
>