another EXPERT LEVEL FORMULA from me

[Guest]

Hi All

A B
1 #N/A d
2 #DIV/0!, h
3 5 k
4 3 t
In Sheet1 I’ve got two columns: Column A and Column B. What I’m tiring to do
is to create a formula to get the value of the first cell in a column “Bâ€
which corresponding (offset) cell in column A is the first row in Column A
with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or
#NULL!
In the example above this is “k†(the value in cell B3).
So far my guided formula is array and look like
this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<>#N/A)))
but it doesn’t work at all. I’m also planning the formula to be in a
different sheet than the range with the data.
Any Help is highly appreciated as always.

Tim

 

[Bob Phillips]

On the right lines

=INDEX(B:B,MIN(IF(ISERROR(A1:A10),9.99999999999999E+307,ROW(A1:A10))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Another PERFECT ANSWER from You.
Thank you Bob!

On the right lines

=INDEX(B:B,MIN(IF(ISERROR(A1:A10),9.99999999999999E+307,ROW(A1:A10))))

--

HTH

RP
(remove nothere from the email address if mailing direct)