MS Excel: Get contents of last cell in a range that has data
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 that uses the INDEX, MATCH and REPT functions:
=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 that uses the INDEX and MATCH functions:
=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 that uses the INDEX, MAX, MATCH and REPT functions:
=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)))
Advertisements