Help with "index" .. I think

[Box666]

I am trying to use a formula to lookup a value. The value I want to
lookup is is the last item in row 16. Which at the moment is G16, but
in a few days it may well have moved to (say) L16.

Is Index the best way to try and find this moving target? or does any
one have a formula to find the last item in specific row .. or the last
item in a column which perhaps I can change to row.

[Max]

One way ...

Try, array-entered (press CTRL+SHIFT+ENTER):
=INDEX(16:16,1,MAX(IF(ISBLANK(16:16),0,COLUMN(16:16))))

Above is adapted from Bob Phillips' fine page at:
http://www.xldynamic.com/source/xld.....html#last_any

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Box666" <[email protected]> wrote in message
news:[email protected]...
> I am trying to use a formula to lookup a value. The value I want to
> lookup is is the last item in row 16. Which at the moment is G16, but
> in a few days it may well have moved to (say) L16.
>
> Is Index the best way to try and find this moving target? or does any
> one have a formula to find the last item in specific row .. or the last
> item in a column which perhaps I can change to row.
>

[Bob Phillips]

=LOOKUP(2,1/(1-ISBLANK(16:16)),16:16)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Box666" <[email protected]> wrote in message
news:[email protected]...
> I am trying to use a formula to lookup a value. The value I want to
> lookup is is the last item in row 16. Which at the moment is G16, but
> in a few days it may well have moved to (say) L16.
>
> Is Index the best way to try and find this moving target? or does any
> one have a formula to find the last item in specific row .. or the last
> item in a column which perhaps I can change to row.
>

[Box666]

Thank you perfect, is it possible to change it to show the last but one
value.(I have tried to "play" with the formula but cannot seem to hit
the right combination).

[Bob Phillips]

=INDEX(16:16,1,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},16:1
6))-1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Box666" <[email protected]> wrote in message
news:[email protected]...
> Thank you perfect, is it possible to change it to show the last but one
> value.(I have tried to "play" with the formula but cannot seem to hit
> the right combination).
>

[Box666]

A big thank you.. I would never have hit that combination

Bob

[Bob Phillips]

The problem was that the previous one did it directly, it didn't determine
the column in the formula. As such, I had to use a different formula, one
that calculated teh column, then just subtract 1.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Box666" <[email protected]> wrote in message
news:[email protected]...
> A big thank you.. I would never have hit that combination
>
> Bob
>