Problem using LOOKUP...

Hi, this seems like it should be really simple but i just can't work it out.

I have one column of figures, the values of which increase and decrease as i add values to the column. So, for example:

200
210
150
180
240
120

Now, i am referencing this column in other cell, and all i want it to do it to find and display the most recent figure added. So using the above data, i want it to show '120'. At the moment, I'm using MAX as the lookup_value, which never felt right and is now proving me right as it's displaying '240' as it's the highest value in the column.

If anybody could point me in the right direction of displaying the latest figure, i would be most obliged!

john

Hi,
This type of Vlookup will display the last value in column B
=VLOOKUP(9.99999999999999E+307,B:B,1)

Say you have your values in column H starting at row H1 and you have unused cells at J1 and K1. In J1 put the formula:

="H" & COUNT(H:H)

in K1 put:

=INDIRECT(J1)

The first formula will create the address of the last used cell in H; the second will use the address built-up in J to fetch the last cell in H.