Using following simple formula is much faster =LOOKUP(2,1/(A:A<>””),A:A) For Excel 2003: =LOOKUP(2,1/(A1:A65535<>””),A1:A65535) It gives you following advantages: it’s not array formula it’s not volatile formula Explanation: (A:A<>””) returns array {TRUE,TRUE,..,FALSE,..} 1/(A:A<>””) modifies this array to {1,1,..,#DIV/0!,..}. Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not … Read more
Unfortunately, there is no formula way to result in a truly blank cell, “” is the best formulas can offer. I dislike ISBLANK because it will not see cells that only have “” as blanks. Instead I prefer COUNTBLANK, which will count “” as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has … Read more
There may be a more eloquent way, but this is the way I came up with: The function to find the last populated cell in a column is: =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ) So if you combine … Read more