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.LastValue.html#last_any

 

[Bob Phillips]

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


--

HTH

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

 

[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]

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)