Is this function supposed to work?

[stevewy]

=VLOOKUP(MAX($A:$A),$A:$A,1)

This function is meant to provide the value displayed in the lower-
most/last active cell in column A. It is provided as a solution to
someone who has a list of figures in a spreadsheet, the last cell of
which is the most up to date figure available and needs to be
displayed elsewhere on the sheet, especially if the last active cell
is constantly changing.

However, I thought MAX( ) would display the largest figure in the
column - not the figure in the largest row number occupied. But the
function does actually work - it displays whatever is in the final row
occupied of column A; even if the last row is the smallest figure in
the column!

Can anyone provide an explanation of why?

Steve

 

[Bernie Deitrick]

Steve,

It does not return the last value in column A, if that value is not the largest (latest) value in
column A..

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

(e-mail address removed) wrote...

=VLOOKUP(MAX($A:$A),$A:$A,1)

If this worked as you're assuming it does, it'd return the same result
as MAX($A:$A). If you want the largest value in col A, just use the
MAX call.

This function is meant to provide the value displayed in the lower-
most/last active cell in column A.  It is provided as a solution to
someone who has a list of figures in a spreadsheet, the last cell of
which is the most up to date figure available and needs to be
displayed elsewhere on the sheet, especially if the last active cell
is constantly changing.

If you want to display the bottommost number value in col A, try

=LOOKUP(9.99999999999999E+307,A:A)

However, I thought MAX( ) would display the largest figure in the
column - not the figure in the largest row number occupied.  But the
function does actually work - it displays whatever is in the final row
occupied of column A; even if the last row is the smallest figure in
the column!

MAX does return the largest number value in its arguments. It's your
VLOOKUP call above that's flawed. Since you omit the optional 4th
argument, VLOOKUP *expects* that col A is sorted in ascending order.
If col A isn't sorted, VLOOKUP will usually return *INCORRECT*
results. Try

=VLOOKUP(MAX($A:$A),$A:$A,1,0)

but, again, it's unnecessary since it would return the same result as
=MAX($A:$A).

 

[T. Valko]

If that formula is returning the correct result it's just "dumb luck".

It sounds like you want the *last* numeric entry from column A.

Try this:

=LOOKUP(1E100,A:A)

To show you that the other formula doesn't work properly try entering these
numbers in A1:A5 - 62,68,10,97,3.

=VLOOKUP(MAX($A1:$A5),$A1:$A5,1)

Result = 97 which is not the last numeric value in the range.

 

[Bernie Deitrick]

I'm sorry - It does return the last value unless you limit the range to a specific row range of the
column - I'm not sure why, but it is likely to be related to how VLOOKUP manages entire column
ranges.

HTH,
Bernie
MS Excel MVP

 

[stevewy]

Thank you, everyone, for your prompt replies. I think I understand
now why it only *appeared* to be working, but if you pick the right
combination of numbers, it soon becomes apparent that it doesn't. I
did wonder, since it appeared to work for me but the colleague I did
it for reported that it did not work for her, so I suspected something
was up.

However, T.Valko's suggestion of =LOOKUP(1E100,A:A) does exactly what
I need, so essentially it's problem solved in this case. Thank you
for the information.

Steve

 

[T. Valko]

You're welcome. Thanks for the feedback!