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