How To Get Last Value in a Column Use Excel Formula

2 minutes to read
103436 0
Table of contents
    3412

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
T10 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 examlple, you can use 9.9E+307
  2. How to input "々"? By holding down the ALT key, typing 41385 on the number pad, then releasing ALT gives you the 々 character.

Recommended content

  1. How To Find The First Number In A Text String Use Excel Formula
  2. How to Sum Data in Excel
  3. How To Count Unique Values In Excel Using Formula
  4. How to Rebuild Data, Case Study
  5. How to Quickly Combine the Contents of Multiple Cells Into One Cell in Excel

Most viewed

  1. Formatting a Range of Cells In Excel VBA
  2. How To Set Column Width In cm At Once
  3. How to Quickly Extract Pictures From An Excel File
  4. Select a Range In Excel VBA
  5. Find and Select the First Blank Cell in a Column VBA
  6. Print Worksheets VBA

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>