Finding the Last value (text or number) in a column

[lezawang]

Hi
In website, he used this vlookup functions to find the last number value and the last text value

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) to get last number

=VLOOKUP("zzz",$A$1:$A$8,1,TRUE) to get the last text


How this works? Why in the first formula he ignored the 3rd argument while in the second one he put the 3rd argument.

Thank you very much.

 

[Rick Rothstein]

Give this formula a try...

=LOOKUP(2,1/(A1:A99<>""),A1:A99)

Adjust the range as needed.

 

[steve the fish]

Simply it doesnt work. Thats a mistake. Presume you meant LOOKUP not VLOOKUP.

 

[Rahul1987]

Coz for the numbers 9.99e+307 this is a maximum value excel can understand instead you can take any larger value which is greater than your data set...for example if you have a data set of 20,25,5000 then you can lookup with 1000 and you will get a outcome

For the text Z is a last alphabet and hence rept("z" , 255) - For "Approximate Match",if the lookup_value is bigger than anything in list, it returns that last position - Big Value

Hope this helps you..sorry for the late reply

Hi
In website, he used this vlookup functions to find the last number value and the last text value

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) to get last number

=VLOOKUP("zzz",$A$1:$A$8,1,TRUE) to get the last text


How this works? Why in the first formula he ignored the 3rd argument while in the second one he put the 3rd argument.

Thank you very much.