LAST VALUE IN A COLUMN

Format the range as TEXT:

=INDEX(range,MATCH(REPT("z",255),range))

Select allOpen in new window

Mixed formatting as TEXT and NUMERIC:

=INDEX(range,MAX(MATCH(9.99999999999999E+307,range),MATCH(REPT("z",255),range)))

Select allOpen in new window

To get the last number:
=LOOKUP(10^307,A:A)

To get the last text:
=LOOKUP("zzzzz",A:A)

To get the last number or text:
=INDEX(A:A,MAX(IFERROR(MATCH(10^307,A:A,1),1),MAX(IFERROR(MATCH("zzzzz",A:A,1),1)))

If you need to ignore cells that contain an empty string =""
=INDEX(A:A,MAX(IF(LEN(A:A)>0,ROW(A:A),""),1))            
This formula must be array-entered, and is slower than the others

This formula will give you the last value in column A - number or text, ignoring blanks or "null strings"

=LOOKUP(2,1/(A:A<>""),A:A)

If there is no value you get #N/A

regards, barry

byundt IFERROR  what's the 2003 alternative for the users that doesnt have 2003.
also what's the difference between 9.99999999999999E+307 and 10^307.thanks

The IFERROR function was added in Excel 2007. The Excel 2003 equivalent formula for either text or number is:
=INDEX(A:A,MAX(IF(ISNA(MATCH(10^307,A:A,1)),1,MATCH(10^307,A:A,1)),IF(ISNA(MATCH("zzzzz",A:A,1)),1,MATCH("zzzzz",A:A,1))))

That said, barryhoudini had a more efficient formula. It won't work with Excel 2003 and earlier because it refers to an entire column, but you could use all but the last row with:
=LOOKUP(2,1/(A$1:A$65535<>""),A$1:A$65535)

The difference between 9.99999999999999E+307 and 10^307 is like the difference between 1 and 9.99999999999999. E+307 is interpreted as 10^307, so the first number is equal to 9.99999999999999 times 10^307.

how do i get the address from =LOOKUP(2,1/(A:A<>""),A:A).Thanks

The LOOKUP function has several features that may not be apparent from looking at the online Help that Microsoft publishes for it:
1. LOOKUP searches only for one type of value. If the first parameter is text, it only searches for text. If the first parameter is a number, it only searches for a number.
2.  LOOKUP ignores any values that aren't of the same type as the first parameter. If the second parameter includes error values, they get ignored.
3.  LOOKUP assumes that the second parameter is sorted in ascending order, but doesn't require that fact. LOOKUP expects to find a series of values that bracket the first parameter. So if the first parameter is 5, it expects the second parameter to be a series of values like 1, 3, 4, 6, 8. In that series it will find the 4, which is the largest value less than the first parameter.
4.  LOOKUP allows an optional third parameter, which is an array of values. When used, it will do the matching with the first and second parameters, then return a corresponding value from the array of values in the third parameter.
5.  If LOOKUP is asked to find a value larger than anything in the second parameter, it returns the last value.
6.  LOOKUP does array-formula type things, but doesn't have to be array-entered (Control + Shift + Enter).

Putting it all together, you don't get an "address" from =LOOKUP(2,1/(A:A<>""),A:A). Instead, you get a value from the third parameter.

In that formula, LOOKUP has three parameters.
=LOOKUP(value to find, values to look in, values to return from)

The first parameter of LOOKUP is the value to find. In this case, the formula is looking for a 2. Because we want the formula to return the last value, the 2 should be larger than any value in the second parameter.

The second parameter of LOOKUP is an array of values to look in. In this case, it is a Boolean expression 1/(A:A<>""). The denominator of this expression (A:A<>"") is an array of TRUE or FALSE values. Because those TRUE or FALSE values are used in an arithmetic operation (1/Boolean value), they are converted to 1 for TRUE and 0 for FALSE. So the entire expression returns an array of either 1 or #DIV/0! error values. As previously mentioned, the error values will be ignored.

The third parameter is the array to return values from. As written the formula is returning a value from column A. In particular, it is returning the last value in column A, regardless of type. And it also ignores empty strings ="".

Getting back to your question about address, if you want values from columns A, B and C on the same row as the last value in column A, you would write the formula as:
=LOOKUP(2, 1/($A:$A<>""), A:A)
When copied across, this latter formula would return first return the last value from column A, then the corresponding values on that same row from columns B and C.

There is no need for LOOKUP to return an address because it can return the desired value(s) directly.

Nice explanation byundt. I would like to use it in a name range also to get the address of the cell with the last value.

Will the named range be a single column? Could it be the whole column?

regards, barry

single column