Is this function supposed to work?
=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 |
Is this function supposed to work?
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 wrote in message ... =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 |
Is this function supposed to work?
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). |
Is this function supposed to work?
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. -- Biff Microsoft Excel MVP wrote in message ... =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 |
Is this function supposed to work?
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ... =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 |
Is this function supposed to work?
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 |
Is this function supposed to work?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... 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 |
All times are GMT +1. The time now is 11:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com