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.

One way ...

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

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" wrote in message
oups.com...
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.

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


--

HTH

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


"Box666" wrote in message
oups.com...
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.

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).

=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" wrote in message
ups.com...
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).

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

Bob

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" wrote in message
ups.com...
A big thank you.. I would never have hit that combination

Bob