Skip to main content

How To Get Last Value in a Column Use Excel Formula

Sometimes you may want to get last value of a Column or a Row with Excel Formula, Here is an example for you. In this example, Column A has mixed with text, number, symbol, error value, blank value, zero, formula, Chinese and Japanese, how to find the Right value? you can copy A1:D11 to a new worksheet to understand this example.

1
2
3
4
5
6
7
8
9
10
11
A B C D
Data Formula Results Description
USA =LOOKUP(9.99999999999999E+307,A:A) 1 return last number
#N/A =INDEX(A:A,MATCH(9.99999999999999E+307,A:A)) 1 return last number
0 =MATCH(9.99999999999999E+307,A:A) 5 return row number of last number
1 =LOOKUP(REPT("z",255),A:A) Excel return last text (only support English)
="" =INDEX(A:A,MATCH(REPT("z",255),A:A)) Excel return last text (only support English)
Excel =MATCH(REPT("z",255),A:A) 7 return row number of last text (English)
=LOOKUP(REPT("々",255),A:A) # return last value
=MATCH(REPT("々",255),A:A) 11 return row number of last value
=LOOKUP(1,0/(A:A<>0),A:A) # return last non zero value
# =LOOKUP(1,0/(NOT(ISBLANK(A:A))),A:A) # return last non blank value

Tips:

  1. 9.99999999999999E+307 is the biggest number in Excel. You can probably use a smaller number than 9.99999999999999E+307, but make sure it is bigger than any number you might use in your file, for example, you can use 9.99E+307
  2. How to input ""? By holding down the ALT key, typing 41385 on the number pad, then releasing ALT gives you the character.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>