# What excel formula can I use to select the last non-error value in a range (that is vertical)?

Ok, for example if my range is B1:B5000. This is a time series. At certain point in time, the values all become errors that are #NUM! So is there any formula I can use to return the last value that is not an error going down the range? Or would it be easier if I got rid of all the errors by using :=IFERROR(B1,NA()) and basically making another error free range? In this case, what excel formula would I use?

-
I posted an answer but note that NA() generates an error value! To eliminate errors and replace with spaces it could be =IFERROR(B1,"") – barry houdini Sep 28 '12 at 13:14

Whether you eliminate errors or not you can find the last number in B1:B5000 (times are numbers in excel) with this formula

`=LOOKUP(9.9E+307,B1:B5000)`

-

The short answer is you can use the following formula to find the last non-error value in a range:

``````=MATCH(ERROR.TYPE(#NUM!),B1:B5000,-1)
``````

This will find the first `#NUM!` error and give you the last row prior, based upon a range of `B1:B5000`. Assuming your last good value was in Row10, it will return a value of 10.

If you're looking to use this in a charting application, or need to use a dynamic range as items are added, it will be more complex.

-