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
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:

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
How to input "々"? By holding down the ALT key, typing 41385 on the number pad, then releasing ALT gives you the 々 character.

Leave a Reply Cancel reply