Formula question: find cell from M29 upwards until a numeric value is found

Assume I have a formula in cell F30 similar to

=$A12 + B$5 + *******

Now I want to find for ******* a way to express the following:

1.) Go to column M in the same row as formula and then one row upwards (here: M29 )
2.) Take that cell value if it contains a (numeric) value.
3.) If it does NOT contain a (numeric) value then go one row upwards (=e.g. M28,M27,M26,......)
4.) If M1 reached take 0 otherwise try again step 2.) above

How can I express that rule in a Excel 2007 formula?

Peter

Hi Peter

Try in F30:

=$A12+B$5+IFERROR(LOOKUP(2,1/ISNUMBER($M$1:M28),$M$1:M28),0)

Originally Posted by lecxe

Try in F30:

=$A12+B$5+IFERROR(LOOKUP(2,1/ISNUMBER($M$1:M28),$M$1:M28),0)

Hi lecxe,
thank you for the suggestion but when I try to apply it I got from Excel 2007:

"The formula you typed contains an error"

with highlighted last (!) "$M$1" reference.

Why did you use a range LOOKUP(.....,$M$1:M28)?
Shouldn't only one cell be returned?

Hi

I just tried it and got no problem.

Can you please try retyping the formula in the cell?
Or do you have non-English settings and you must separate the function parameters with a semicolon ";" instead of with a comma ","?

Originally Posted by pstein

Why did you use a range LOOKUP(.....,$M$1:M28)? Shouldn't only one cell be returned?

Yes, only one value is returned, the one corresponding to the last match.