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)
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 value of text is higher than value of number (try =”a”>9.99999999999999E+307 in any cell. It will return TRUE) So when you use a list with numbers as well 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.
How to make VLOOKUP Case Sensitive. How to Use VLOOKUP with Multiple Criteria. VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here!. Use IFERROR with VLOOKUP to Get Rid of #N/A Errors. Find the Last Occurrence of a Lookup Value a List in Excel.
