Share this page:

MS Excel: Get contents of last cell in a range that has data in Excel 2010/2007/2003/XP/2000/97

Question: In Microsoft Excel 2010/2007/2003/XP/2000/97, how do I make a cell display the contents of the last cell of a range that has data?

Answer: This is a bit tricky and the formula that you need to use will depend on the types of data that you have in the range.

Data range is formatted as text

If your range contains only text values, you could use the following formula:

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

where range is the range that you wish to check

For example, if you wanted to find the last text value in column A, you would use the following formula:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

For example, if you wanted to find the last text value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(REPT("z",255),C2:C10))

Data range is formatted as numeric

If your range contains only numbers, you could use the following formula:

=INDEX(range,MATCH(9.99999999999999E+307,range))

where range is the range that you wish to check

For example, if you wanted to find the last numeric value in column A, you would use the following formula:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

For example, if you wanted to find the last numeric value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(9.99999999999999E+307,C2:C10))

Data range contains both text and numeric

If your range contains both text and numeric values (ie: there must be at least one text and one numeric value in your range) and you want to find the last value, you could use the following formula:

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

where range if the range that you wish to check

For example, if you wanted to find the last value (either text or numeric) in column A, you would use the following formula:

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

For example, if you wanted to find the last value (either text or numeric) in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MAX(MATCH(9.99999999999999E+307,C2:C10),MATCH(REPT("z",255),C2:C10)))