Excel has its own limitations, and in this blog post, I will show you how we can use one of the limitations to our advantage.
Get the Last Number in a List in Excel using Excel VLOOKUP Function
The largest positive number that you can use in Excel is 9.99999999999999E+307 (I tried this in Excel 2010).
That’s huge!!
I don’t think you would ever need any calculation involving such a large number. And that is exactly what we can use get the last number in a list.
Suppose you have a dataset (in A1:A14) as shown below and you want to get the last number in the list.
Here is the formula you can use:
=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE)
How this works:
- List with NUMBERS only
Note that I have used an approximate match VLOOKUP (notice TRUE at the end of the formula, instead of FALSE). This means that if the formula spots the exact match of the look-up value, it returns that value. Otherwise, it keeps on going one cell after the other and stops when the value in the next cell is higher. It then returns the value of the cell where it stopped.
We use this property by putting the largest possible number excel can handle as the look-up value. So, excel would keep on going till the last cell, and when it can’t find anything larger than this, it returns the value in the last cell.
- List with NUMBERS and TEXT both
This formula would work with a list that has numbers as well as text. Here we use the fact that in Excel, the value of a text is higher than the value of a number (try =”a”>9.99999999999999E+307 in any cell. It will return TRUE)
So when you use a list with numbers as well as text and use the above formula, it scans the entire list one cell at a time.
It stops when it can not find a number bigger than 9.99999999999999E+307. Since the value of text is considered higher than numbers in Excel, this formula would correctly return the last number from the list.
You May Also Like the Following VLOOKUP Tutorials:
3 thoughts on “Use VLookup to Get the Last Number in a List in Excel”
I love you!! I created a checking account spreadsheet years ago and because some month would have longer transactions then others, I never could find a formula to automatically pull the last transaction amount from the last month’s tab to start my new month’s tab. This was so simple to do, once I saw your formula I laughed at myself!
Thanks man.. sometimes it’s these small things that make our life so easy.. Glad this formula worked for you
Thanks today I have learned a new formula.