If there ARE blank cells in the range then use one of the following:
1. Range has NUMBERS only
=INDEX(2:2,MATCH(9.999999E+306,2:2))
2. Range has TEXT only
=INDEX(2:2,,MATCH("*",2:2,-1))
3. MIXED range with both numbers and text
=INDEX(2:2,,IF(ISERROR(MATCH(9.999999E+306,2:2)),MATCH("*",2:2,-1), IF(ISERROR(MATCH("*",2:2,-1)),MATCH(9.999999E+306,2:2), MAX(MATCH(9.999999E+306,2:2),MATCH("*",2:2,-1)))))