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:

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

How To Get Last Value in a Column Use Excel Formula

Recommended content

Leave a commentCancel