another EXPERT LEVEL FORMULA from me
 

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

another EXPERT LEVEL FORMULA from me
 

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 EXPERT LEVEL FORMULA from me
 

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