Hi All

A B
1 #N/A d
2 #DIV/0!, h
3 5 k
4 3 t
In Sheet1 Ive got two columns: Column A and Column B. What Im 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 doesnt work at all. Im also planning the formula to be in a
different sheet than the range with the data.
Any Help is highly appreciated as always.

Tim

On the right lines

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

--

HTH

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


"Tim" wrote in message
...
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

Another PERFECT ANSWER from You.
Thank you Bob!


"Bob Phillips" wrote:

On the right lines

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

--

HTH

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


"Tim" wrote in message
...
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