Excel - find last value in a column which is not an error or blank

Question

I need to find in a column of an excel spreadsheet the last non-error, non-blank value. The column can have several instaces of #N/A which are alternating with actual values. There are several solutions for finding the last non-empty cell, but the solutions do not consider errors, in particular if the last non-empty cell has got an error.

The solution would be 3 in this case.

Answer 1

Try this formula

=LOOKUP(2,1/(A1:A100<>""),A1:A100)

extend range as required. In Excel 2007 and later you can use the whole column