Array Formula
 

=INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW
(J16:J21)),1),1)

Why will this formula not work when I change the cells to
the ones above in this formula? Its like the formula will
not work unless the data starts on row 1.


-----Original Message-----
=INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW
(B1:B200)),1),1)

Entered with Ctrl+Shift+Enter Rather than Just Enter
since it is an array
formula.

--
Regards,
Tom Ogilvy

Todd Huttenstine
wrote in message
...
ColumnA ColumnB ColumnC
C 1 *formula?
D 2
B 3
E 4
F 5
A #N/A


Above is the situation. I have formulas in column A and
in Column B. The formulas in Column A will produce a
letter. The formulas in Column B will produce a number.
An #N/A error will be in only one of the cells in
Column B
in the specified range. I need a formula in cell C1
that
will look in Columns A and B and when it sees the #N/A
error in the cell in Column B, it will return the
corresponding letter in Column A.

So in this example, the formula in cell C1 will produce
the result A, because the #N/A error is in cell B6.

Array Formula
 

Answered you original posting of this question. Please stay in the thread.

--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
=INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW
(J16:J21)),1),1)

Why will this formula not work when I change the cells to
the ones above in this formula? Its like the formula will
not work unless the data starts on row 1.


-----Original Message-----
=INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW
(B1:B200)),1),1)

Entered with Ctrl+Shift+Enter Rather than Just Enter
since it is an array
formula.

--
Regards,
Tom Ogilvy

Todd Huttenstine
wrote in message
...
ColumnA ColumnB ColumnC
C 1 *formula?
D 2
B 3
E 4
F 5
A #N/A


Above is the situation. I have formulas in column A and
in Column B. The formulas in Column A will produce a
letter. The formulas in Column B will produce a number.
An #N/A error will be in only one of the cells in
Column B
in the specified range. I need a formula in cell C1
that
will look in Columns A and B and when it sees the #N/A
error in the cell in Column B, it will return the
corresponding letter in Column A.

So in this example, the formula in cell C1 will produce
the result A, because the #N/A error is in cell B6.