# 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.